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

Re: AT TIME ZONE: "convert"?

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Martijn van Oosterhout <kleptog(at)svana(dot)org>,David Garamond <lists(at)zara(dot)6(dot)isreserved(dot)com>,Postgres general mailing list <pgsql-general(at)postgresql(dot)org>
Subject: Re: AT TIME ZONE: "convert"?
Date: 2004-11-28 05:12:24
Message-ID: 200411280512.iAS5COu14073@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
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(at)svana(dot)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(at)postgresql(dot)org so that your
>       message can get through to the mailing list cleanly
> 

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

In response to

pgsql-general by date

Next:From: mstoryDate: 2004-11-28 05:24:56
Subject: Re: SERIAL error
Previous:From: Stephan SzaboDate: 2004-11-28 05:11:54
Subject: Re: SERIAL error

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