Re: "timestamp without timezone" and at "time zone"

From: "Stefan Murphy" <stefan(at)vocalocity(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: "timestamp without timezone" and at "time zone"
Date: 2008-10-17 17:43:04
Message-ID: 925169557BAB6947A70CB145F894A75B58FE02@mail-41ps.atlarge.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
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)

Browse pgsql-novice by date

  From Date Subject
Next Message Ennio-Sr 2008-10-17 17:56:14 How to transform pg_7.4 function 'to_number' to 8.4 equivalent?
Previous Message Stefan Murphy 2008-10-17 17:32:28 "timestamp without timezone" and at "time zone"