Skip site navigation (1) Skip section navigation (2)

Re: timestamp with/without time zone

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: thomas(at)pgsql(dot)com
Cc: lockhart(at)fourpalms(dot)org, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Peter Eisentraut <peter_e(at)gmx(dot)net>, PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: timestamp with/without time zone
Date: 2001-09-05 04:28:00
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
Thomas, any status on this?  If not, I should add it to the TODO list.

> > 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

  Bruce Momjian                        |
  pgman(at)candle(dot)pha(dot)pa(dot)us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

In response to


pgsql-hackers by date

Next:From: Bruce MomjianDate: 2001-09-05 04:42:35
Subject: Re: Bug in createlang?
Previous:From: Bruce MomjianDate: 2001-09-05 03:59:48
Subject: Re: [GENERAL] DBD::Pg errstr method doesn't return full error messages

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group