Re: AT TIME ZONE: "convert"?

Added TODO description:

* Allow TIMESTAMP WITH TIME ZONE to store the original timezone
  information, either zone name or offset from UTC

  If the TIMESTAMP value is stored with a time zone name, interval
  computations should adjust based on the time zone rules, e.g. adding
  24 hours to a timestamp would yield a different result from adding one
  day.


---------------------------------------------------------------------------

Tom Lane wrote:
> Martijn van Oosterhout <kleptog@svana.org> writes:
> > On Mon, Nov 01, 2004 at 11:00:10AM -0500, Tom Lane wrote:
> >> It does not really.  By my reading of SQL99, the result should always be
> >> timestamptz, and the behavior when the input is already timestamptz
> >> should be that the new timezone spec is inserted while preserving the
> >> same absolute time (UTC-equivalent timestamp).
>
> > That's quite a different use of timestamptz. Does the SQL standard
> > decide what defines a timestamp with a timezone, does it only allow
> > the 'number of hours relative to UTC' or does it also allow different
> > places in the world.
>
> The SQL spec thinks that a timezone is a numeric offset from UTC, full stop.
>
> My vision of what we will actually support is either numeric offsets or
> named time zones --- basically, anything that you can now say either in
> SET TIMEZONE or as a zone name in timestamptz input ought to work in
> both places (as well as in AT TIME ZONE's second parameter).  And a
> stored timestamptz value ought to retain the full information about what
> zone spec was given (for instance it should remember "PST8PDT" not just
> "PST").  There was extensive discussion about this just last week.
>
> > That's an interesting one, Is Australia/Sydney before or after
> > Australia/Brisbane. It is questionable if there is any meaningful order
> > to timezones. Alphabetical will make no-one happy, by
> > longatude/latitude is way too complex. Maybe base offset, then
> > alphabetical.
>
> We can probably arrange to sort by UTC offset, but the sort order within
> equal UTC offsets will likely be arbitrary (basically in order of the
> numeric identifiers we assign to time zone names ... though that might
> be user-configurable to some extent).
>
> > It's a backward incompatable change (or is it?)
>
> Some things will break, no doubt, but I don't think it will be too bad.
> Certainly no worse than the changes we've made in these data types in
> prior releases to move them closer to SQL spec.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073