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.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
