Thread

Re: timestamp with/without time zone

Thomas Lockhart <lockhart@alumni.caltech.edu> — 2001-07-11T01:35:34Z

> Is this a TODO item?

Sure, but I'd hate to have all of these individual items showing up as
separate things in some ToDo list, since it won't paint a coherent
picture of where things are headed.

I'm planning on doing some work on timestamp, which will include:

o support for "ISO variants" on input, including embedded "T" preceeding
the time fields

o deprecation of 'current' (holdover from Original Postgres)

o deprecation of 'invalid' for timestamp at least (holdover from
Original Postgres)

o (probably) deprecation of "ignored fields" if the value not explicitly
recognized (holdover from Original Postgres)

o resolution of the SQL99 vs SQL-useful timestamp and timestamp with
time zone issues

The latter has two possible outcomes (maybe more):

a) we keep the current timestamp implementation as either timestamp or
timestamp with time zone, and implement the other as a new type with
much common underlying code

b) we roll back decisions made a few years ago, and have "SQL-useful
timestamp" become datetime, leaving timestamp with time zone and
timestamp with slavish SQL99 compliance as undersupported, ineffective
and near-useless data types (an overstatement for simple timestamp, but
not for timestamp with time zone).

For those who haven't used a fully compliant timestamp with time zone
(and most haven't, since it is brain damaged) the time zone is specified
as a single offset from GMT. No provisions for DST, etc etc.

The current identification of timestamp as "timestamp with time zone"
was to prepare for implementation of a "no time zone anywhere" timestamp
in 7.2. The current timestamp would become "timestamp with time zone",
with time zone support substantially enhanced from SQL99 specs. I'll
speak for the silent majority to claim that these enhancements are
useful. They are likely compatible enough (or could be) to pass SQL9x
compliance testing, unless that testing includes cases which try to
enforce the worst aspects of the standard.

Hmm, now that I look at it again, SQL99 timestamp with time zone may not
be too far away from our current timestamp, except for issues concerning
UTC vs local time and probably some other details of formatting and
behavior (e.g. allowed date ranges; we allow more).

It appears that SQL99 timestamp with time zone outputs as UTC (which is
how it is stored internally already) so the standard is missing the
notion of representing time zones in the output of a timestamp or
timestamp with time zone type. This is not as horrendous as SQL92 or as
described in some draft standard docs, but... Comments?

                         - Thomas