From: | Josh Kupershmidt <schmiddy(at)gmail(dot)com> |
---|---|
To: | Rob Richardson <Rob(dot)Richardson(at)rad-con(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Timestamp confusion |
Date: | 2010-09-28 15:02:40 |
Message-ID: | AANLkTi=0S1sfbzPaz80MbuZM4dee_tPTHc7XvJRg9c7P@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On Tue, Sep 28, 2010 at 9:23 AM, Rob Richardson
<Rob(dot)Richardson(at)rad-con(dot)com> wrote:
> Greetings!
>
> I have a table with a column declared to contain a timestamp with time
> zone. A database function inserts a row into this table using the
> following statement:
>
> insert into alarm_hold (charge, hold_code, hold_generated,
> condition_date) values (ChargeNum, '0471', 0, current_timestamp at time
> zone 'UTC');
I don't think you should be using the "at time zone 'UTC'" part. Just
use CURRENT_TIMESTAMP if you're inserting into a "timestamp with time
zone" column, and let Postgres keep track of the time zone conversions
for you.
> The resulting value contained in the timestamp field is 2010-09-28
> 13:09:27.015-04.
>
> Since I am in the Eastern time zone of the United States and daylight
> savings time is in effect, the -04 indicates that it is 4 hours earlier
> for me than it is in Greenwich, England. And this value was generated
> at 9:09 this morning local time, so the 13:09 is understandable.
That value means "1:09 PM in Eastern time". The "-04" you see tells
you that Postgres is giving you the time with a 4-hour offset from
UTC, and is controlled by the "timezone" guc -- use "SHOW timezone;"
to check. So I don't think that's what you want. Just use
CURRENT_TIMESTAMP in your INSERTs and you should get what you expect.
Josh
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2010-09-28 15:05:09 | Re: Timestamp confusion |
Previous Message | Rob Richardson | 2010-09-28 15:00:28 | Re: Timestamp confusion |