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@zigo.dhs.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@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073