>>>>> "Kevin" == Kevin Grittner <kevin.gritt...@wicourts.gov> writes:
>> Given that the spec requires that 2009-01-31 + interval 1 month = >> 2009-02-31 (yes, really! see general rule 4 in subsection 6.30), I >> think we can safely ignore virtually everything it says about >> date/time handling. Kevin> Codd went on at some length about why this is the right thing Kevin> to do. He was highly critical of systems where adding a month Kevin> to a date and then subtracting month from the result could Kevin> result in a date which was off from the original date by as Kevin> much as three days. As a mathematician he felt strongly that Kevin> "(x + y) - y" should equal x -- even when x is a date and y is Kevin> an interval. Mathematical elegance is all very well, but until you convince the real world to abandon inelegant concepts like months with unequal lengths, the database has to behave in ways that are useful within the constraints of actual practice. (To me, the fact that the spec's idea of 2009-01-31 + 1 month corresponds to a value that current_date will never be equal to is a far greater show-stopper.) To look specifically at timezones, the problem with the spec here is that it doesn't store _timezones_, it stores _timezone offsets_. So per the spec, (timestamp with time zone '2009-01-01 12:00:00 +0000' + interval 6 months) would be equal to '2009-07-01 12:00:00 +0000' (REGARDLESS of what the server's timezone is configured as), which is remarkably non-useful; also, the spec's idea of + interval 24 hours is equal to + interval 1 day, whereas here in the real world those aren't the same thing at all. Worse still, the spec defines the behaviour of DST as follows: the server has a specific timezone _offset_, that offset _changes_ on DST start/end, and conversions between timestamp w/o tz and timestamptz are done USING THE SERVER'S CURRENT OFFSET, NOT THE OFFSET AS IT WOULD HAVE BEEN AT THE TIME VALUE BEING CONVERTED. This is so wrong there aren't even words to describe how wrong it is. Kevin> Personally, I think that including time zone in the TIMESTAMP Kevin> WITH TIME ZONE data type would go a long way toward making Kevin> some useful features work. It would break far too many other things in the process. If you want to store both a timestamp and an associated timezone you can do it right now, using a composite type or two columns, with the advantage that you get semantics that you can rely on. -- Andrew. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
