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)

Search Discussions

  • Stefan Murphy at Oct 17, 2008 at 5:44 pm
    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)
  • Tom Lane at Oct 17, 2008 at 6:37 pm

    "Stefan Murphy" <stefan@vocalocity.com> writes:
    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?
    It's operating as intended, which is not compatible with your
    expectation. AT TIME ZONE on a timestamp WITHOUT tz means "assume that
    the given timestamp is local time in this timezone. Rotate it to UTC
    and return that as a timestamp WITH tz". For display, the result gets
    rotated to your display timezone (TimeZone setting), which evidently is
    UTC. So 16:58 in EDT is in fact 20:58 in UTC.

    I think the behavior you're after is probably the one associated with
    the reverse transform, ie start from a timestamp WITH tz and get one
    without.

    In general, if you're trying to store a column that represents actual
    instants in time, the column type should be timestamp with tz. When you
    use timestamp without tz the interpretation changes depending on the
    timezone setting, which is almost surely not what you want.

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedOct 17, '08 at 5:44p
activeOct 17, '08 at 6:37p
posts3
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Stefan Murphy: 2 posts Tom Lane: 1 post

People

Translate

site design / logo © 2022 Grokbase