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/
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 ... |
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 |