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

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

>
> Best regards,
>
> depesz

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com