Unique constraint on a date looks for previous day

Rails
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”)+00.hours+00.minutes+00.seconds

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