Re: Why extract( ... from timestamp ) is not immutable?

On Wednesday, January 25, 2012 7:22:25 am hubert depesz lubaczewski wrote:
> hi,
> Question is basically in the title, but let's show some example:
>
> $ begin;
> BEGIN
>
> *$ set timezone = 'EST';
> SET
>
> *$ select now(), extract(epoch from now()), extract(epoch from now() at
> time zone 'UTC'); now              │    date_part     │    date_part
> ───────────────────────────────┼──────────────────┼──────────────────
>  2012-01-25 10:19:17.366139-05 │ 1327504757.36614 │ 1327522757.36614
> (1 row)
>
> *$ set timezone = 'CET';
> SET
>
> *$ select now(), extract(epoch from now()), extract(epoch from now() at
> time zone 'UTC'); now              │    date_part     │    date_part
> ───────────────────────────────┼──────────────────┼──────────────────
>  2012-01-25 16:19:17.366139+01 │ 1327504757.36614 │ 1327501157.36614
> (1 row)
>
> Why aren't the 3rd date_parts the same in both cases? I mean - I see that
> they are adjusted due to timezone, but why is it happening?
>
> Based on \dt+, I seem to see that it should be immutable:
> *$ \df+ date_part

Its not the extract part but the at time zone part see:

http://www.postgresql.org/docs/9.0/interactive/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT

--
Adrian Klaver
adrian.klaver@gmail.com