Re: Is TimeZone applied with TIMESTAMP WITHOUT TIME ZONE and Extract( EPOCH ...)? - Mailing list pgsql-general

From bubba postgres
Subject Re: Is TimeZone applied with TIMESTAMP WITHOUT TIME ZONE and Extract( EPOCH ...)?
Date
Msg-id AANLkTi=UpE-KvDVu00cNXabB1L7HExPSESqg16z4OozO@mail.gmail.com
Whole thread Raw
In response to Re: Is TimeZone applied with TIMESTAMP WITHOUT TIME ZONE and Extract( EPOCH ...)?  (bubba postgres <bubba.postgres@gmail.com>)
List pgsql-general
ok got it.

select EXTRACT( EPOCH FROM '2010-04-01 00:00:00'::TIMESTAMP WITHOUT TIME ZONE at time zone 'utc' );


On Thu, Mar 17, 2011 at 11:32 AM, bubba postgres <bubba.postgres@gmail.com> wrote:
no.. still confused.
I assume it's storing everythign in UTC.. did I need to specify a timezone when I inserted?



On Thu, Mar 17, 2011 at 11:24 AM, bubba postgres <bubba.postgres@gmail.com> wrote:
Looks like a quick search says I need to specify the timezone...


On Thu, Mar 17, 2011 at 11:22 AM, bubba postgres <bubba.postgres@gmail.com> wrote:

I'm noticing some interesting behavior around timestamp and extract epoch, and it appears that I'm getting a timezone applied somewhere.

Specifically, If I do:
select EXTRACT( EPOCH FROM '2010-01-31 00:00:00'::TIMESTAMP WITHOUT TIME ZONE ); == 1264924800
select EXTRACT( EPOCH FROM '2010-04-01 00:00:00'::TIMESTAMP WITHOUT TIME ZONE ); == 1270105200

Now if I do something similar in Java.. using a GregorianCalendar, with "GMT" TimeZone.
I get
Hello:2010-01-31 00:00:00.000 (UTC)
Hello:1264896000000

Hello:2010-04-01 00:00:00.000 (UTC)
Hello:1270080000000

Which gives a difference of 8 and 7 hours respectively, so both a timezone and a DST shift are at work here.

Is this the expected behavior of extract epoch, is there a way to get it to always be in GMT?







pgsql-general by date:

Previous
From: bubba postgres
Date:
Subject: Re: Is TimeZone applied with TIMESTAMP WITHOUT TIME ZONE and Extract( EPOCH ...)?
Next
From: Scott Marlowe
Date:
Subject: Re: Primary key vs unique index