Unique constraint on a date looks for previous day

rubyonrails-talk
1

I'm saving some objects on the DB that should be unique for a given date.

So I have added this to the model:

validates_uniqueness_of :related_date, :scope => [:indicator_id, :edone_id]

So an object with a given indicator_id and edone_id for a given related date can only exist once in the DB

I have added an index to the table like this:

add_index :kpivalues, [:related_date, :indicator_id, :edone_id], :unique => true

Everything seems to go ok and if I try to create an object with the same related date it fails.

BUT since I added that constraint the objects are created one day before (!?)

If the related_date is 2012-11-23 I can see form the logs that the check for the existing value is actually looking for the 22nd

(DEBUG) 15489 Kpivalue Exists (0.4ms) SELECT 1 FROM "kpivalues" WHERE ("kpivalues"."related_date" = '2012-11-22 22:00:00.000000' AND "kpivalues"."indicator_id" = 1485 AND "kpivalues"."edone_id" IS NULL) LIMIT 1

Any ideas why it is looking for the previous day?

Thanks.

2

comopasta Gr wrote in post #1086663:

If the related_date is 2012-11-23 I can see form the logs that the check for the existing value is actually looking for the 22nd

(DEBUG) 15489 Kpivalue Exists (0.4ms) SELECT 1 FROM "kpivalues" WHERE ("kpivalues"."related_date" = '2012-11-22 22:00:00.000000' AND "kpivalues"."indicator_id" = 1485 AND "kpivalues"."edone_id" IS NULL) LIMIT 1

Well I can see the 2012-11-22 22:00:00.000000 is the UTC date of 2012-11-23 00:00:00 in my timezone. So I guess I need to pass the date considering time difference. Not sure which one yet.

3

comopasta Gr wrote in post #1086665:

comopasta Gr wrote in post #1086663:

If the related_date is 2012-11-23 I can see form the logs that the check for the existing value is actually looking for the 22nd

(DEBUG) 15489 Kpivalue Exists (0.4ms) SELECT 1 FROM "kpivalues" WHERE ("kpivalues"."related_date" = '2012-11-22 22:00:00.000000' AND "kpivalues"."indicator_id" = 1485 AND "kpivalues"."edone_id" IS NULL) LIMIT 1

Well I can see the 2012-11-22 22:00:00.000000 is the UTC date of 2012-11-23 00:00:00 in my timezone. So I guess I need to pass the date considering time difference. Not sure which one yet.

I modified the input date to be: d = Date.parse("2012-01-25")+23.hours+59.minutes

I'm not sure if this is the appropriate fix though. Maybe someone has a better approach.