Re: timestamp with time zone tutorial

On Sunday 19 July 2009 10:59:24 pm Dennis Gearon wrote:
> Hey Tom,
> I was trying to use 'US/Pacific-New' as my long, unabbreviated
> timezone and it wasn't working. I thought postgres wasn't accepting the
> unabbreviated, geopolitical, daylight savings time, time zones. Turns out,
> the server that I was on, (not my own box), didn't have that in the
> '/usr/share/zoneinfo/US' directory. My bad.
>
> I just have to read more on how to get it out relative to a different
> time zone than it went in. I'll find it.
>
>
> Dennis Gearon
>

For me it easier to think of the time zones as a formatting option rather than a
data storage attribute. The simple explanation is that it always goes in as
UTC. The more complicated explanation follows. The tz data types are stored as
UTC. The time zone information is used on input to make the correct offset from
the specified tz to UTC. On output the procedure is reversed. Since the data is
stored as UTC you get to choose whatever time zone you want it displayed as by
using the appropriate setting or function. The issue is if it is important to
know the tz that was used for the input offset. That would require a separate
field. You can search the archives for the many discussions that have occurred
on this point.

--
Adrian Klaver
aklaver(at)comcast(dot)net