Skip site navigation (1) Skip section navigation (2)

"timestamp without timezone" and at "time zone"

From: "Stefan Murphy" <stefan(at)vocalocity(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: "timestamp without timezone" and at "time zone"
Date: 2008-10-17 17:32:28
Message-ID: 925169557BAB6947A70CB145F894A75B58FDFD@mail-41ps.atlarge.net (view raw or flat)
Thread:
Lists: pgsql-novice
I'm seeing some odd behavior (to me) around a timestamp without timezone
column.  Was hoping someone could shed some light on this for me.  I
insert current_timestamp into the column.  When I select the value it is
the expected UTC time.  When I select the value with "AT TIME ZONE EDT"
I expected it to return in eastern time which is UTC - 4, but is
returning as UTC + 4.  Is AT TIME ZONE incompatable with this data type?

Postgres 8.1.11
OS time is UTC
Postgres timezome is UTC

I did the below example at 12:58 eastern time.  UTC time was 16:58.

I have a table with a column timestamp without timezone.

matthew/[local] hdap=# \d  billing.stefan_test;
           Table "billing.stefan_test"
 Column |            Type             | Modifiers
--------+-----------------------------+-----------
 dt     | timestamp without time zone |

I insert a record.
INSERT INTO billing.stefan_test(dt)
    VALUES (CURRENT_TIMESTAMP);

matthew/[local] hdap=# select dt, dt AT TIME ZONE 'EDT' from
billing.stefan_test;
             dt             |           timezone
----------------------------+-------------------------------
 2008-10-17 16:58:51.240091 | 2008-10-17 20:58:51.240091+00    <<< Was
expecting 12:58:51
(1 row)




Responses

pgsql-novice by date

Next:From: Stefan MurphyDate: 2008-10-17 17:43:04
Subject: Re: "timestamp without timezone" and at "time zone"
Previous:From: Bruce HyattDate: 2008-10-17 15:36:04
Subject: Re: [pgsql-novice] Daily digest v1.2311 (11 messages)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group