Re: timestamp with time zone a la sql99

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