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

From: hubert depesz lubaczewski <depesz(at)depesz(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Why extract( ... from timestamp ) is not immutable?
Date: 2012-01-25 15:22:25
Message-ID: 20120125152225.GA979@depesz.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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/

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2012-01-25 15:35:47 Re: Why extract( ... from timestamp ) is not immutable?
Previous Message James Robinson 2012-01-25 15:17:44 9.0.6 "cluster" transient failure ...

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2012-01-25 15:23:14 Re: GUC_REPORT for protocol tunables was: Re: Optimize binary serialization format of arrays with fixed size elements
Previous Message Alvaro Herrera 2012-01-25 14:17:27 Re: Avoid FK validations for no-rewrite ALTER TABLE ALTER TYPE