Re: Timezones (in 8.5?) - Mailing list pgsql-hackers

Robert Haas <robertmhaas@gmail.com> wrote:
> On Wed, Nov 18, 2009 at 11:18 PM, Andrew Gierth
> <andrew@tao11.riddles.org.uk> wrote:
>>>>>>> "Kevin" == "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
writes:
>>
>>  >> If he meant (A), then you store the event as:
>>  >> (ts,tz) = (timestamp '2010-07-27 10:30:00',
>>  >> 'Chile/Santiago')
>>
>>  >> If he meant (B), then you store the event as
>>  >> (tsz,tz) = (timestamp '2010-07-27 10:30:00' at time zone
>>  >> 'Chile/Santiago', 'Chile/Santiago')
>>
>>  Kevin> You seem to be agreeing that these problems can't be solved
>>  Kevin> without storing a time zone string in addition to the
>>  Kevin> timestamp.  As I read it, Hernán was wishing for types
>>  Kevin> which include this, rather than having to do the above
>>  Kevin> dance with multiple values.
>>
>> Right, but including more data in a single type is the wrong
>> approach, since it complicates the semantics and interferes with
>> normalization.
Or, one could say, it encapsulates the semantics within the type's
operators, avoiding the need to repeat the logic everywhere, or to use
more verbose explicit function calls.
>> For example, if you have a type T which incorporates a timestamp
>> and a timezone, what semantics does the T = T operator have?  What
>> semantics apply if the definitions of timezones change?
I'd rather sort that out once and implement the desired semantics in
the operators for a new type than to count on application programmers
doing it consistently each time.  Wouldn't you?
>> What if you're storing times of events at specific places; in that
>> case you want to associate the timezone with the _place_ not the
>> event (so that if the timezone rules change, moving the place from
>> one timezone to another, you only have to change the place, not all
>> the events that refer to it).
I'm not sure I quite followed you there, but Hernán's example
specifically called for storing 'Chile/Santiago', not a UTC offset or
something as easily changed as the 'CLT' or 'CLST' time zone
designations -- so it is tied to a place rather more closely than
anything else.  I think that was part of his point -- that for civil
time you care about what the clock on a typical business's wall at
that place will read on that date, regardless of what changes might
happen in time zone definitions.
> Also, if someone DOES want to use these together, isn't that what
> composite types are for?
I'm going to plead both ignorance and laziness here.  My use of
composite types is limited, so I don't know, offhand, whether you can
define a set of operators for a composite type which will provide the
consistent behavior with convenient operators which Hernán seems to
want.  If they allow that, then it certainly seems like the way to go,
so that the component parts of the abstraction we've been calling
civil time can be easily accessed.  If not, they're not suited to what
Hernán wants (as I understand it).
For the record, this discussion has made me realize that I don't care
as much about including such information with tsz as with ts.  The tsz
enhancement wouldn't change the semantics of the object at all, as far
as I can see, beyond it's default presentation when you turn it into a
string.  That's worth something, but pales in comparison to the value
of the civil time concept, which would actually match the common usage
in scheduling business meetings and most other every-day activities.
I think the popularity of physical time is that it is so concrete.
The reality of usage of date and time, though, is that various
abstractions which aren't tightly coupled to physical time are common
and useful.  The civil time issues are one aspect of that.  (And as
far as I'm concerned, leap seconds can be totally ignored for civil
time -- there's a nice round clock up on my wall with a big hand and
a little hand and a second hand all spinning around, and there's no
place on that clock face for a 61st or 62nd second in any minute,
ever.)  And those who don't think it's useful be able to add one month
to the 31st of January and get a date as a result to which you can add
one month and get the 31st of March -- well, come the cultural
revolution I plan to see to it that they do nothing but write
financial applications for five years....  :-)
-Kevin