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

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

From: Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>
To: depesz(at)depesz(dot)com
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Why extract( ... from timestamp ) is not immutable?
Date: 2012-01-25 16:54:44
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-generalpgsql-hackers
On Wednesday, January 25, 2012 8:30:17 am hubert depesz lubaczewski wrote:
> 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.

If you mean that the result will be different depending on the timezone set then 
yes.  My argument, and it seems moot now, is that the function is immutable but 
the data is not.  That you get different results because you pass in different 
data. That timestamps other than UTC are relative and with out being very 
specific what time you are dealing with the results can vary.  I would agree that 
probably needs to be spelled out better.

> Personally, I think that extract(epoch from timestamp) should assume
> that the timestamp is UTC.

What if it isn't?

> Or that there should be a way to do it - by "it" i mean - extract epoch
> value from timestamp value in immutable way.

Have a timezone value on the timestamp. If the data you are working with is 
stored as timestamp with time zone then the timestamps represent a point in 

> Best regards,
> depesz

Adrian Klaver

In response to


pgsql-hackers by date

Next:From: Robert HaasDate: 2012-01-25 16:57:22
Subject: Re: [v9.2] sepgsql's DROP Permission checks
Previous:From: Tom LaneDate: 2012-01-25 16:40:28
Subject: Re: GUC_REPORT for protocol tunables was: Re: Optimize binary serialization format of arrays with fixed size elements

pgsql-general by date

Next:From: hubert depesz lubaczewskiDate: 2012-01-25 16:57:50
Subject: Re: Why extract( ... from timestamp ) is not immutable?
Previous:From: Tom LaneDate: 2012-01-25 16:30:49
Subject: Re: Why extract( ... from timestamp ) is not immutable?

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