Re: LOCALTIMESTAMP has wrong time zone

Solved.

Changing the field datatype from TIMESTAMP to TIMESTAMPTZ fixed it. Now I
can use now() as the default value.

Strange that it just cropped up recently, but you're right we should be
including time zone with that timestamp anyways.

My deep gratitude for your time and help!

JB

-----Original Message-----
From: Kevin Grittner [mailto:Kevin(dot)Grittner(at)wicourts(dot)gov]
Sent: Friday, February 25, 2011 11:15 AM
To: JB(at)BlackSkyTech(dot)com; pgsql-bugs(at)postgresql(dot)org; 'Tom Lane'
Subject: RE: [BUGS] LOCALTIMESTAMP has wrong time zone

"Jonathan Brinkman" <JB(at)BlackSkyTech(dot)com> wrote:

> ## I COULDN'T MAKE IT BREAK USING PSQL.

That's pretty solid evidence that the problem isn't in the
PostgreSQL server.

> This didn't always happen, it just started happening on various of
> my tables a maybe couple weeks or so ago. I think it is related to
> an update, either to Ubuntu 10.04 or Postgresql 8.4. I usually
> apt-get update/upgrade whenever I see that updates are available.

I would look at /var/log/dpkg.log to see what you installed at the
point when things broke.

> Also, this only occurs on my production server (Rackspace cloud).
> My dev postgres server doesn't do this timestamp time-zone problem
> at all.

I would be taking a close look at what the differences are.
Anything that is the same on both servers can't be the problem,
right?

I'm going to harp on one other point -- you will almost certainly be
better off if you make these columns TIMESTAMP WITH TIME ZONE. This
is the type which is meant to represent moments in the stream of
time. It will behave as you probably expect in many more
circumstances, especially when recording when events occurred.
WITHOUT TIME ZONE is mostly useful for scheduling future events
which you want to happen at different points in time in different
time zones, or for scheduling things which should occur in whatever
time is in effect locally when the related date arrives.

-Kevin