From: | Randall Nortman <postgreslists(at)wonderclown(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Best practices: Handling Daylight-saving time |
Date: | 2005-03-12 17:22:38 |
Message-ID: | 20050312172238.GA16478@li2-47.members.linode.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | bruce | 2005-03-12 18:13:34 | postgresql error |
Previous Message | Tom Lane | 2005-03-12 16:57:16 | Re: postgres 8 settings |