Re: timestamp with time zone a la sql99

Added to TODO:

* Once we expand timestamptz to bigger than 8 bytes, there's essentially

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

Tom Lane wrote:
> Dennis Bjorklund <db(at)zigo(dot)dhs(dot)org> writes:
> > So if I understand you correctly you are planning to extend the current
> > timestamp type to work with both named time zones and HH:MM ones? I didn't
> > think you wanted the last one since your plan was to store a UTC+OID where
> > the OID pointed to a named time zone. And I guess that you don't plan to
> > add 00:00, 00:01, 00:02, ... as named zones with an OID.
>
> I missed getting back to you on this, but I think we can do both. Some
> random points:
>
> * Once we expand timestamptz to bigger than 8 bytes, there's essentially
> zero cost to making it 12 bytes, and for that matter we could go to 16
> without much penalty, because of alignment considerations. So there's
> plenty of space.
>
> * What we need is to be able to represent either a fixed offset from UTC
> or a reference of some kind to a zic database entry. The most
> bit-splurging way of doing the former is a signed offset in seconds from
> Greenwich, which would take 17 bits. It'd be good enough to represent
> the offset in minutes, which needs only 11 bits.
>
> * I suggested OIDs for referencing zic entries, but we don't have to do
> that; any old mapping table will do. 16 bits would surely be plenty to
> assign a unique label to every present and future zic entry.
>
> * My inclination therefore is to extend timestamptz with two 16-bit
> fields, one being the offset from UTC (in minutes) and one being the
> zic identifier. If the identifier is zero then it's a straight numeric
> offset from UTC and the offset field is all you need (this is the SQL
> spec compatible case). If the identifier is not zero then it gives you
> an index to look up the timezone rules. However, there is no need for
> the offset field to go to waste; we should store the offset anyway,
> since that might save a trip to the zic database in some cases.
>
> * It's not clear to me yet whether the stored offset in the second case
> should be the zone's standard UTC offset (thus always the same for a
> given zone ID) or the current-time offset for the timestamp (thus
> different if the timestamp is in daylight-savings or standard time).
>
> * If we store the current-time offset then it almost doesn't matter
> whether the timestamp itself is stored as a UTC or local time value;
> you can trivially translate either to the other by adding or subtracting
> the offset (*60). But I'm inclined to store UTC for consistency with
> past practice, and because it will make comparisons a bit faster: you
> can compare the timestamps without adjusting first. Generally I think
> comparisons ought to be the best-optimized operations in a Postgres
> datatype, because index operations will do a ton of 'em. (We definitely
> do NOT want to have to visit the zic database in order to compare two
> timestamptz values.)
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

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