"date_part('epoch', now() at time zone 'UTC')" not the same time as "now() at time zone 'UTC'" in po
- by sirlark
I'm writing a web based front end to a database (PHP/Postgresql) in which I need to store various dates/times. The times are meant to be always be entered on the client side in the local time, and displayed in the local time too. For storage purposes, I store all dates/times as integers (UNIX timestamps) and normalised to UTC. One particular field has a restriction that the timestamp filled in is not allowed to be in the future, so I tried this with a database constraint...
CONSTRAINT not_future CHECK (timestamp-300 <= date_part('epoch', now() at time zone 'UTC'))
The -300 is to give 5 minutes leeway in case of slightly desynchronised times between browser and server.
The problem is, this constraint always fails when submitting the current time. I've done testing, and found the following.
In PostgreSQL client:
SELECT now() -- returns correct local time
SELECT date_part('epoch', now()) -- returns a unix timestamp at UTC (tested by feeding the value into the date function in PHP correcting for its compensation to my time zone)
SELECT date_part('epoch', now() at time zone 'UTC') -- returns a unix timestamp at two time zone offsets west, e.g. I am at GMT+2, I get a GMT-2 timestamp.
I've figured out obviously that dropping the "at time zone 'UTC'" will solve my problem, but my question is if 'epoch' is meant to return a unix timestamp which AFAIK is always meant to be in UTC, why would the 'epoch' of a time already in UTC be corrected? Is this a bug, or I am I missing something about the defined/normal behaviour here.
