Re: Timestamp confusion

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