Re: 'NOW' in UTC with no timezone

On Tue, Oct 12, 2004 at 10:43:09AM -0400, Tom Lane wrote:
> Greg Stark <gsstark(at)mit(dot)edu> writes:
> > I guess my confusion comes from the way postgres interprets unadorned time
> > stamps as being in local time. And then always displays timestamps converted
> > to local time. I thought it was remembering the time zone specified in the
> > original input. In fact it's not doing that.
>
> Indeed not. (I think that the SQL spec contemplates that TIMESTAMP WITH
> TIME ZONE *should* work that way, but that's not what we've done.)

In something I'm working on at the moment I've settled on storing the
timestamp and the timezone in seperate columns. The reason is that it
really needs to represent time in a particular timezone. The operation
of adding one day to a timestamp is dependant on a particular timezone
due to daylight savings. If everything is always rotated to your
current timezone the results will just be wrong...

Since PostgreSQL doesn't actually support daylight savings timezones
I'm going to do the processing in the application. I'd consider adding
it to PostgreSQL too except this needs to work on pre-8.0 systems.

Maybe what is needed is a TIMESTAMP WITH FIXED TIME ZONE type :)

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.