Re: to_timestamp() and timestamp without time zone - Mailing list pgsql-general

On 06/23/2011 02:45 PM, David Johnston wrote:
> ...
> As for "Time handling has lots of subtleties that take time to digest"; a
> good programmer and API do their best to minimize the number of hidden
> subtleties to be learned....

I meant that time-calculations themselves have lots of issues and
subtleties.

The length of a day, month and a year are all varying periods of time
leading to things like adding then subtracting a month does not return
the original date:
select '2011-01-31'::date + '1 month'::interval - '1 month'::interval;
       ?column?
---------------------
  2011-01-28 00:00:00

The 30/360 accounting method takes care of this by simply assuming that
every month has 30 days and there are 360 days in a year. There are
plenty of shift-work systems and contracts that simply decree a shift to
be 8-hours regardless if your shift is actually 7- or 9-hours due to DST
changeover.

Since DST changes are not synchronized to a common point-in-time
worldwide, one can easily attempt to scheduled synchronized
early-morning jobs between East and West coast that will fail when the
East changes DST several hours ahead of the West. Even tracking what
rule to apply is tricky. Parts of Arizona observe daylight savings.
Others do not. And while we're at it, what about those pesky leap-seconds?

Calculations for long prior dates/times have things like a few minute
jump when (at least in the US) an interval crosses Sunday, November 18,
1883 ("the day of two noons"). And although October 1582 (Catholic
regions) or September 1752 (Protestant regions/Unix-assumption) or later
(Orthodox) are missing 10-days, PostgreSQL follows the SQL standard
which does not show those dates as missing at all.

There is also an assumption that date calculations continue backward in
history prior to the actual development of the concept of time-zones.
And, lacking prescience, calculations for future dates assume that
time-zone definitions won't change so the answer you get today may not
be the answer you get if you run the same calculation tomorrow.

There are different definitions of when a year starts so be sure not to
grab the wrong week-number or day-number - ISO and Julian are not the same.

And, of course, everything starts with the ethnocentric assumption of
what calendar system to use. From my experience, there is not a lot of
good SQL support for data using Islamic, Chinese, Hebrew, Hindu,
Iranian, Coptic or Ethiopian calendars.

Until one considers which of the many issues inherent to date
calculation may be important, one will not even know what assumptions to
check for in the software being used.

Cheers,
Steve