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...
CHECK (timestamp-300 <= date_part('epoch', now() at time zone 'UTC'))
SELECT date_part('epoch', now())
SELECT date_part('epoch', now() at time zone 'UTC')
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.
select ((now() at time zone 'UTC') at time zone '<your_time_zone>') at time zone 'UTC';