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

On 03/17/2011 05:05 PM, bubba postgres 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

Looks reasonable to me. It appears that you are in the US/Pacific time
zone. Per the docs, "Conversions between timestamp without time zone and
timestamp with time zone normally assume that the timestamp without time
zone value should be taken or given as timezone local time. A different
time zone can be specified for the conversion using AT TIME ZONE. "

Eliminating the "extract" from your statements so you can see the actual
values:

select
TIMESTAMP '2010-01-01 00:00:00'  as defhour,
TIMESTAMP '2010-01-01 00:00:00' at time zone 'PST' as psthour,
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;

defhour  | 2010-01-01 00:00:00
psthour  | 2010-01-01 00:00:00-08
utchour  | 2009-12-31 16:00:00-08
utcepoch | 1262304000

So if your timezone is set to PST8PDT, TIMESTAMP '2010-01-01 00:00:00'
means that you are displaying that time, in local Pacific time, and not
including any timezone information.

The second one is taking that same time, assumed to be your local
Pacific time, and specifying a time zone (in this case the same as your
default time) thus causing the result to be a type timestamptz (note the
-08).

The third case specifies that the time given is in UTC - basically
'2010-01-01 00:00:00-00'. Again the result is a timestamptz. And that
point in time, from the PST8PDT perspective is 2009-12-31 16:00:00-08.

.select timestamptz '2010-01-01 00:00:00-00';
       timestamptz
------------------------
  2009-12-31 16:00:00-08

However, if you take a timestamp *with* time zone and specifically ask
for it to be displayed in a different (or same) time zone the result
with be a timestamp *without* time zone with the timestamp being correct
for the zone you requested.

BTW, utcepoch doesn't really make sense - the epoch is identical
regardless of time zone since it is the number of seconds since January
1 1970 00:00:00 UTC which is the same everywhere.

So you need to be *very* careful in understanding the actual date you
are specifying and the way you are displaying it.

You may want to stick with timestamptz as your data type but beware of
another foot-gun. Here's your original query changing to timestamptz:

select
TIMESTAMPTZ '2010-01-01 00:00:00'  as defhour,
TIMESTAMPTZ '2010-01-01 00:00:00' at time zone 'PST' as psthour,
TIMESTAMPTZ '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;
-[ RECORD 1 ]--------------------
defhour  | 2010-01-01 00:00:00-08
psthour  | 2010-01-01 00:00:00
utchour  | 2010-01-01 08:00:00
utcepoch | 1262304000

Still probably not what you wanted since timestamptz '2010-01-01
00:00:00' is assumed to include the '-08' offset but the timestamptz
gets converted to timestamp with the 'at time zone'.

You probably want to use timestamptz as the data type and include the
zone in the source data:
select
TIMESTAMPTZ '2010-01-01 00:00:00'  as defhour,
TIMESTAMPTZ '2010-01-01 00:00:00 PST8PDT' as psthour,
TIMESTAMPTZ '2010-01-01 00:00:00 UTC' as utchour;

defhour | 2010-01-01 00:00:00-08
psthour | 2010-01-01 00:00:00-08
utchour | 2009-12-31 16:00:00-08

Then you can convert whatever fully qualified time you specify into
whatever time zone you want using "at time zone" or by using 'set
timezone to ..."

Cheers,
Steve


>
>
>
>
>     @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