Skip site navigation (1) Skip section navigation (2)

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

From: hubert depesz lubaczewski <depesz(at)depesz(dot)com>
To: Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Why extract( ... from timestamp ) is not immutable?
Date: 2012-01-25 16:30:17
Message-ID: 20120125163017.GA13120@depesz.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
On Wed, Jan 25, 2012 at 08:22:26AM -0800, Adrian Klaver wrote:
> The issue seems to be the definition of same arguments. Since epoch is anchored 
> at 1970-01-01 00:00:00 UTC the timestamp passed to extract need to be normalized 
> to UTC.  Once a timestamp is in UTC then the epoch can be determined. The 
> variability lies in the initial data fed to the function. Since time does not 
> stand still, every time you do now() you are getting a different argument. Throw 
> in time zone considerations and you see the results you are getting. 

??? Sorry?
what are you talking about?

Simple:
extract(epoch from '2012-01-01 12:34:56'::timestamp)
which doesn't contain now(), is not immutable.

Personally, I think that extract(epoch from timestamp) should assume
that the timestamp is UTC.
Or that there should be a way to do it - by "it" i mean - extract epoch
value from timestamp value in immutable way.

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2012-01-25 16:30:49
Subject: Re: Why extract( ... from timestamp ) is not immutable?
Previous:From: Alvaro HerreraDate: 2012-01-25 16:25:46
Subject: Re: pg_trigger_depth() v3 (was: TG_DEPTH)

pgsql-general by date

Next:From: Tom LaneDate: 2012-01-25 16:30:49
Subject: Re: Why extract( ... from timestamp ) is not immutable?
Previous:From: Adrian KlaverDate: 2012-01-25 16:22:26
Subject: Re: Why extract( ... from timestamp ) is not immutable?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group