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: pgsql-general(at)postgresql(dot)org, depesz(at)depesz(dot)com
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: Why extract( ... from timestamp ) is not immutable?
Date: 2012-01-25 15:44:14
Message-ID: 201201250744.15234.adrian.klaver@gmail.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
On Wednesday, January 25, 2012 7:37:27 am hubert depesz lubaczewski wrote:
> On Wed, Jan 25, 2012 at 10:35:47AM -0500, Tom Lane wrote:
> > hubert depesz lubaczewski <depesz(at)depesz(dot)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?

Isn't extract(epoch from now()) getting what you want?

> 
> Best regards,
> 
> depesz

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

In response to

Responses

pgsql-hackers by date

Next:From: hubert depesz lubaczewskiDate: 2012-01-25 15:44:44
Subject: Re: Why extract( ... from timestamp ) is not immutable?
Previous:From: Tom LaneDate: 2012-01-25 15:43:59
Subject: Re: Why extract( ... from timestamp ) is not immutable?

pgsql-general by date

Next:From: hubert depesz lubaczewskiDate: 2012-01-25 15:44:44
Subject: Re: Why extract( ... from timestamp ) is not immutable?
Previous:From: Tom LaneDate: 2012-01-25 15:43:59
Subject: Re: Why extract( ... from timestamp ) is not immutable?

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