From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
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:05:09 |
Message-ID: | 24564.1285686309@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
"Rob Richardson" <Rob(dot)Richardson(at)rad-con(dot)com> writes:
> 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');
> 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.
> The thing I am confused about is why the "-04" is there.
You declared the column as timestamp with time zone. The AT construct
produced a timestamp without time zone, viz "2010-09-28 13:09:27.015",
which the system then had to convert to timestamp with time zone; which
it did using the assumption that local time was meant.
Basically, you don't want to use AT TIME ZONE here; it loses information
to no good purpose. Just insert the result of current_timestamp and
call it good.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Kupershmidt | 2010-09-28 15:21:29 | Re: Timestamp confusion |
Previous Message | Josh Kupershmidt | 2010-09-28 15:02:40 | Re: Timestamp confusion |