Re: TIMESTAMP WITHOUT TIME ZONE - Mailing list pgsql-general

On Fri, Dec 15, 2006 at 12:10:24PM -0800, Richard Troy wrote:
> > I think your fundamental error is in using timestamp without time zone
> > in the database.  Try with-time-zone if you want consistent results
> > across clients in different zones.
>
> I sure hope there's no issue with using timestamp without timezone
> _anywhere_ in the PG world because, quite frankly, "timezone" just doesn't
> cut it.
>
> There are so many issues that I don't think I have time to justice
> to them here in a short email, but, just so we've all got some idea:
> First, you need at least minute, if not second offset from GMT to have
> anything like a comprehensive shot at "timezone." Hour-based time zones
> are simply insufficient. There are lots of places in the world with
> non-hour offsets from GMT.

I don't think you understand the meaning of "timestamp with timezone".
No timezone is stored. What is stored it number of seconds since epoch
and that is rotated to the client's timezone on display.

> The second biggest issue is probably the plethora of "daylight savings
> time" schemes - and they change over time: notably within the last year, a
> U.S. community muffed handling such a chnage with their Canadian
> neighbors. And there are the timings of changes, too - do automated
> daemons know when the time changes? It's quite different in various parts
> of the world. Do you blindly follow your system clock? LOTS of questions
> here that are none of PG's business, but are vital to a production system
> always getting it right.

Quite, which is why the timezone is not stored, too ambiguous.
Automated daemons only use seconds since epoch. hour/minutes/seconds are
for people, not computers.

> Third, any presumption about when which version of a time should be valid
> is bound to cause major errors at some point or another. One can't just
> always hand the user a timestamp in local time on client ends because you
> don't know what kind of local processing they might wish to do outside of
> the database engine, not the least of which is the type of question, "was
> it after their business hours?" - a local-to-local question! Therefore, as
> a minimum, you _must_ provide transform functions, one to the other, and
> let the caller ask for what they wanted. This is particularly tricky when
> it comes to database join statements - did you give the query the GMT
> version, or local version?! -oy- The headaches this can cause, even among
> experts.

There are transform functions, the AT TIME ZONE 'blah' construct will
convert between "timestamp with timezone" and "timestamp without
timezone".

For join statements, postgres doesn't let you compare the with and
without timezone variants. You have to specify the timezone you wish to
compare in, or the comparison has no meaning.

> We at Science Tools use "timestamp without timezone" as the basis of our
> handling our customer's data correctly. It's configurable, but by default
> all data going into a database is converted to GMT by our software,
> outside the database engine, unless explicitly directed otherwise. To
> handle the optionality of this, all join operations happen using what we
> call "database time", so if a db doesn't store in GMT for some reason, we
> still know what to do (for example, converting to the equivalent local
> time of the server). We track client's GMT offsets - stored in the db, of
> course - so we've got every client's offset data when needed, etc, etc,
> etc.

You can do it that way. Alternatively, you can give the timezone to
postgres and let it do the conversion to GMT. That at least has the
advantage that you know all clients are using the same timezone
definitions.

Internally, postgres only uses GMT.

> ...I PRESUME there's nothing broken about "timestamp without timezone"
> within either the engine or the JDBC drivers, but I'd also caution to
> always punt on the question of whether or not someone should or shouldn't
> use Postgres' time zone feature. Perhaps a "for most people" qualifier,
> or, "when every user is in an hour-offset from GMT timezone", etc., but
> even then, multi-time-zone applications need to be VERY warry.

It looks like you understand the issues, so if you're using it
correctly, fine. Most people do not, and many try to use "timestamp
without timezone" to store local times, which does not work.

The good rule of thumb is basically:
- timestamp with timezone is for recording an instant in time (seconds
since epoch).
- timestamp without timezone is for recording what appears on a clock
face, that will be constant wherever the client is.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.