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
List of functions
Schema │ Name │ Result data type │ Argument data types
│ Type │ Volatility │ Owner │ Language │ Source
code │ Description
────────────┼───────────┼──────────────────┼───────────────────────────────────┼────────┼────────────┼───────┼──────────┼──────────────────────────────────────────────────────────────────────────┼─────────────────────────────────────────────
pg_catalog │ date_part │ double precision │ text, abstime
│ normal │ stable │ pgdba │ sql │ select pg_catalog.date_part($1,
cast($2 as timestamp with time zone)) │ extract field from abstime
pg_catalog │ date_part │ double precision │ text, date
│ normal │ immutable │ pgdba │ sql │ select pg_catalog.date_part($1,
cast($2 as timestamp without time zone)) │ extract field from date
pg_catalog │ date_part │ double precision │ text, interval
│ normal │ immutable │ pgdba │ internal │ interval_part
│ extract field from interval
pg_catalog │ date_part │ double precision │ text, reltime
│ normal │ stable │ pgdba │ sql │ select pg_catalog.date_part($1,
cast($2 as pg_catalog.interval)) │ extract field from reltime
pg_catalog │ date_part │ double precision │ text, timestamp without time zone
│ normal │ immutable │ pgdba │ internal │ timestamp_part
│ extract field from timestamp
pg_catalog │ date_part │ double precision │ text, timestamp with time zone
│ normal │ stable │ pgdba │ internal │ timestamptz_part
│ extract field from timestamp with time
zone
pg_catalog │ date_part │ double precision │ text, time without time zone
│ normal │ immutable │ pgdba │ internal │ time_part
│ extract field from time
pg_catalog │ date_part │ double precision │ text, time with time zone
│ normal │ immutable │ pgdba │ internal │ timetz_part
│ extract field from time with time zone
(8 rows)
Best regards,
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
