Re: why does extract ( HOUR FROM TIMESTAMP '2010-01-01 00:00:00' at time zone 'utc' ) == 16?

I found a work around... Not sure why this is the behavior
select extract ( HOUR FROM (TIMESTAMP '2010-01-01 00:00:00' at time zone
'utc' ) at time zone 'utc' ) gives what I expect would be the correct
answer....
BUT..
select extract ( EPOCH FROM (TIMESTAMP '2010-01-01 00:00:00' at time zone
'utc' ) at time zone 'utc' ) does not...

Can anyone explain this?

On Thu, Mar 17, 2011 at 5:05 PM, bubba postgres <bubba(dot)postgres(at)gmail(dot)com>wrote:

> Is this the correct behavior? It seems like if I specify the utc offset it
> should be 0, not 16.. It seems to be the opposite behavior from extract
> epoch.
>
> select extract ( HOUR FROM TIMESTAMP '2010-01-01 00:00:00' ) as defhour,
> extract ( HOUR FROM TIMESTAMP '2010-01-01 00:00:00' at time zone 'PST' ) as
> psthour, extract ( HOUR FROM TIMESTAMP '2010-01-01 00:00:00' at time zone
> 'utc' ) as utchour, extract ( epoch FROM TIMESTAMP '2010-01-01 00:00:00' at
> time zone 'utc' ) as utcepoch;
>
> 0,0,16,1262304000
>
>
>
>
> @Test
> public void testFoo() {
> TimeZone tz = TimeZone.getTimeZone("GMT");
> GregorianCalendar cal = new GregorianCalendar(tz);
> cal.set(2010,0,1,0,0,0);
> cal.set(GregorianCalendar.MILLISECOND, 0 );
> System.out.println("" + cal.getTimeInMillis() );
> System.out.println("" + String.format( "%1$tY-%1$tm-%1$td
> %1$tH:%1$tM:%1$tS.%1$tL", cal ) );
> System.out.println("" + cal.get(GregorianCalendar.HOUR_OF_DAY ) );
> }
>
> In Java:
> 1262304000000
> 2010-01-01 00:00:00.000 (UTC)
> 0
>