"date_part('epoch', now() at time zone 'UTC')" not the same time as "now() at time zone 'UTC'" in postgresql

Question

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.

Answer 1

The "now() at time zone 'UTC'" value is your current time moved to UTC and then converted to TIMESTAMP WITHOUT TIME ZONE.

So you give "date_part" a TIMESTAMP WITHOUT TIME ZONE (unknown time zone in other words) and expect to receive the difference in seconds between it and a fixed timestamp at known time zone (the "EPOCH", 1970-01-01 00:00:00 UTC).

Postgres needs TIMESTAMP WITH TIME ZONE to calculate this. So it converts your value to time zone 'UTC' assuming it's at your time zone and calculates the difference.

Something like:

select ((now() at time zone 'UTC') at time zone '<your_time_zone>') at time zone 'UTC';