Re: Why extract( ... from timestamp ) is not immutable? - Mailing list pgsql-general

From hubert depesz lubaczewski
Subject Re: Why extract( ... from timestamp ) is not immutable?
Date
Msg-id 20120125153727.GA23595@depesz.com
Whole thread Raw
In response to Re: Why extract( ... from timestamp ) is not immutable?  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses 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>)
List pgsql-general
On Wed, Jan 25, 2012 at 10:35:47AM -0500, Tom Lane wrote:
> hubert depesz lubaczewski <depesz@depesz.com> writes:
> > 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?
>
> Given a timestamp without time zone, timestamp_part('epoch') assumes
> that it is in session timezone, and rotates it back to UTC so as to
> satisfy the expectation that epoch values start from zero at midnight
> UTC.  In short, the calculation you're showing does the zone correction
> an extra time.  Don't do that.

ok.
how can I then have immutable epoch for given point in time?

I thought that this is what I will achieve with extract(epoch from now()
at time zone 'UTC') but clearly it doesn't work.
So what options do I have?

Best regards,

depesz

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

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Why extract( ... from timestamp ) is not immutable?
Next
From: Adrian Klaver
Date:
Subject: Re: Why extract( ... from timestamp ) is not immutable?