Mike Harding wrote:
mvh=> set time zone 'UTC'; SET mvh=> select now(); now ------------------------------- 2007-02-13 03:37:35.660652+00 (1 row)
"+00" says your database thinks you are in Greenwich.
mvh=> select timestamp with time zone '2007-01-01' at time zone 'America/Los_Angeles'; timezone --------------------- 2006-12-31 16:00:00 (1 row)
- "timestamp with time zone '2007-01-01'" = midnight at your location.
mvh=> select timestamp '2007-01-01' at time zone 'America/Los_Angeles'; timezone ------------------------ 2007-01-01 08:00:00+00 (1 row)
- "timestamp '2007-01-01' at time zone 'America/Los_Angeles'" is midnight in Los Angeles.
Where does that extra 8 hours come from?
In the first case, the data base is telling you the local time in Los Angeles equivalent to midnight your local time; in the second case it is telling you the local time at your location equivalent to midnight in Los_Angeles.
