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

On Wednesday, January 25, 2012 8:08:37 am hubert depesz lubaczewski wrote:
> On Wed, Jan 25, 2012 at 08:06:42AM -0800, Adrian Klaver wrote:
> > And therein lies the problem:) Per Toms comment, extract sees these
> > timestamps without timezones and assumes they are local time and rotates
> > them back to UTC.
>
> i know about it.
> but - given the fact that date_part(, timestamp) is marked as immutable,
> it seems to be that it's a bug.

http://www.postgresql.org/docs/9.0/static/xfunc-volatility.html

An IMMUTABLE function cannot modify the database and is guaranteed to return the
same results given the same arguments forever. This category allows the
optimizer to pre-evaluate the function when a query calls it with constant
arguments. For example, a query like SELECT ... WHERE x = 2 + 2 can be simplified
on sight to SELECT ... WHERE x = 4, because the function underlying the integer
addition operator is marked IMMUTABLE.

http://www.postgresql.org/docs/9.0/interactive/functions-
datetime.html#FUNCTIONS-DATETIME-EXTRACT
epoch
For date and timestamp values, the number of seconds since 1970-01-01 00:00:00
UTC (can be negative); for interval values, the total number of seconds in the
interval

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.

>
> Best regards,
>
> depesz

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