On Wednesday, January 25, 2012 7:44:44 am hubert depesz lubaczewski wrote: > On Wed, Jan 25, 2012 at 07:37:44AM -0800, Adrian Klaver wrote: > > Its not the extract part but the at time zone part see: > > > > http://www.postgresql.org/docs/9.0/interactive/functions-datetime.html#FU > > NCTIONS-DATETIME-ZONECONVERT > > not sure what you mean - timestamptz at time zone converts to timestamp > (without time zone), and it shows predictable results: > $ begin; > BEGIN > > *$ set timezone = 'EST'; > SET > > *$ select now() at time zone 'UTC'; > timezone > ──────────────────────────── > 2012-01-25 15:43:31.048171 > (1 row) > > *$ set timezone = 'CET'; > SET > > *$ select now() at time zone 'UTC'; > timezone > ──────────────────────────── > 2012-01-25 15:43:31.048171 > (1 row) > > both timestamps returned are the same. And therein lies the problem:) Per Toms comment, extract sees these timestamps without timezones and assumes they are local time and rotates them back to UTC. To illustrate, I am in PST: test(5432)aklaver=>select now() at time zone 'UTC'; timezone --------------------------- 2012-01-25 16:03:47.32097 test(5432)aklaver=>select extract(epoch from '2012-01-25 16:03:47.32097'::timestamp at time zone 'UTC'); date_part ------------------ 1327507427.32097 test(5432)aklaver=>SELECT extract(epoch from ('2012-01-25 16:03:47.32097'::timestamp + interval '8 hrs')); date_part ------------------ 1327565027.32097 > > Best regards, > > depesz -- Adrian Klaver adrian.klaver@xxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
