Re: Best practices: Handling Daylight-saving time

On Sat, Mar 12, 2005 at 05:44:52PM +0100, Karsten Hilbert wrote:
> On Fri, Mar 11, 2005 at 01:43:21PM -0500, Randall Nortman wrote:
>
> > As others have mentioned, store timestamps on the server in UTC,
>
> 1) As long as I store them as <timestamp with time zone> I should
> not need to care what they are stored as on the backend as
> long as I provide the proper timezone for the client location.
> Correct ?
>
> 2) If I then retrieve them as "... at time zone <...>" I will get
> the equivalent time in the time zone of the retrieving client.
> The same could be be achieved with "set timezone" per session.
> Correct ?

Yes and Yes


> 3) If I retrieve them without "at time zone" I will get them with
> the time zone that was stored in the first place, right ?
[...]

This would be news to me.  I don't think it's possible to *not* have a
timezone set on a session.  The server will have a default timezone
based either on the local (server) system time or the setting of the
timezone variable in postgresql.conf.  Additionally, libpq
applications will, I believe, issue a "set timezone" during initial
connection setup.  The manual (section 8.5.3) seems to indicate that
libpq will only do that if PGTZ is set, but I seem to recall it
happening without PGTZ.  (But I'm not entirely sure; feel free to
experiment.)

Anyway, afaik, Postgres does not store the "original" timezone
anywhere, and so could not possibly retrieve it.  I think the only
physical difference between the "timestamp" and "timestamp with time
zone" types is in the system catalog; the manual states that both of
them store 8 bytes and have the same range.  If "timestamp with time
zone" were storing anything extra, I would think the storage size
would be greater or else the range smaller.

Randall