Is TimeZone applied with TIMESTAMP WITHOUT TIME ZONE and Extract( EPOCH ...)?
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?
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:
Show quoted text
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 ); == 1270105200Now 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:1264896000000Hello:2010-04-01 00:00:00.000 (UTC)
Hello:1270080000000Which 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?
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:
Show quoted text
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 ); == 1270105200Now 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:1264896000000Hello:2010-04-01 00:00:00.000 (UTC)
Hello:1270080000000Which 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?
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:
Show quoted text
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 ); == 1270105200Now 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:1264896000000Hello:2010-04-01 00:00:00.000 (UTC)
Hello:1270080000000Which 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?