Hibernate date field with a unique constraint on the day

I'm currently imple开发者_JS百科menting a historical report store where users will store only 1 report per day. We're trying to apply unique constraints but we're struggling as the created date goes down to millisecond resolution. Is there anyway to apply a constraint to just the day of a Date field in Hibernate without having to drop down to apply the constraint directly in our underlying Postgres DB?

The code that doesn't work currently looks like:

@Entity
@Table(name = "report", uniqueConstraints = @UniqueConstraint(columnNames={"createdDate", "name"})) 
public class Report {

  @Column
  private Date createdDate

  @Column   
  private String name;

  @Id   
  @GeneratedValue(strategy = GenerationType.SEQUENCE)   
  private Long id;

}

Have you tried putting @Temporal(TemporalType.DATE) on createdDate? I think that annotation will tell Hibernate to generate a Date column (instead of one holding a complete timestamp).

You could

• Insert a index as proposed by StarShip3000 as a database-object in Hibernate mappings
• Add another property to the class which is private and mapped to the database to a unique field and returns only the date of creation. If you need the creation time as well, implement an empty setter.
• Map the field to a database datatype which only stores dates (I don't know postgres, but assume that there is such a data type). If you don't need the creation time, it's all you need.

I don't know hibernate, but you can create this constraint directly on the database table using good old straightforward SQL.

Such as:

CREATE UNIQUE INDEX uidx_report_name_create_date ON report (name,date_trunc('day', create_date));



select date_trunc('day', TIMESTAMP '2001-02-16 20:38:40');

RETURNS 2001-02-16 00:00:00

select date_trunc('day', TIMESTAMP '2001-02-16 10:38:40');

RETURNS 2001-02-16 00:00:00