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

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

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

On 03/18/2011 10:17 AM, bubba postgres wrote:
> Thank you for your thorough reply. It will take some time to digest
> your advice, but yes, I am specifically trying to avoid all TZ issues
> by using UTC everywhere all the time. My assumption was that Timestamp
> without timezone meant UTC, guess not.
>
> Regards,
> -JD
>

If you need to deal with multiple time zones, you can't avoid TZ issues.
But be aware that regardless of how you specify a point in time
(timestamp with time zone), PostgreSQL stores it internally at UTC and
that point in time can be displayed in any time zone you wish.

create table tzexamp (mytimestamp timestamptz);

-- The following are equivalent (based on my default timezone of
US/Pacific):
insert into tzexamp values (timestamptz '2010-01-01 00:00:00');
insert into tzexamp values (timestamptz '2010-01-01 08:00:00-00');
insert into tzexamp values (timestamptz '2010-01-01 03:00:00 EST5EDT');
insert into tzexamp values (abstime(1262332800));
insert into tzexamp values (timestamptz 'January 1 02:00:00 2010
posix/America/Chicago');
set timezone to 'Asia/Macao';
insert into tzexamp values (timestamptz '2010-01-01 16:00:00');
set timezone to default;


select * from tzexamp;
       mytimestamp
------------------------
  2010-01-01 00:00:00-08
  2010-01-01 00:00:00-08
  2010-01-01 00:00:00-08
  2010-01-01 00:00:00-08
  2010-01-01 00:00:00-08
  2010-01-01 00:00:00-08

But for the client connecting from Japan:

set timezone to 'Asia/Tokyo';

select * from tzexamp;
       mytimestamp
------------------------
  2010-01-01 17:00:00+09
  2010-01-01 17:00:00+09
  2010-01-01 17:00:00+09
  2010-01-01 17:00:00+09
  2010-01-01 17:00:00+09
  2010-01-01 17:00:00+09

Or, of course, GMT:

set timezone to 'UTC';

select * from tzexamp;
       mytimestamp
------------------------
  2010-01-01 08:00:00+00
  2010-01-01 08:00:00+00
  2010-01-01 08:00:00+00
  2010-01-01 08:00:00+00
  2010-01-01 08:00:00+00
  2010-01-01 08:00:00+00

Cheers,
Steve