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

Поиск
Список
Период
Сортировка
От hubert depesz lubaczewski
Тема Why extract( ... from timestamp ) is not immutable?
Дата
Msg-id 20120125152225.GA979@depesz.com
обсуждение исходный текст
Ответы Re: Why extract( ... from timestamp ) is not immutable?  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: Why extract( ... from timestamp ) is not immutable?  (Adrian Klaver <adrian.klaver@gmail.com>)
Список pgsql-general
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/

В списке pgsql-general по дате отправления:

Предыдущее
От: James Robinson
Дата:
Сообщение: 9.0.6 "cluster" transient failure ...
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Why extract( ... from timestamp ) is not immutable?