Forum: Ruby on Rails Unique constraint on a date looks for previous day

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.

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.

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.