Re: timestamp patch to extend legal range of dates. - Mailing list pgsql-patches

On Tue, 2003-02-04 at 16:09, Rod Taylor wrote:
> > 1752 (but which Unix are you speaking of here, John?  Most of 'em don't
> > keep time before ~1900, period).  I'd be inclined to follow that
> > precedent not strike out on our own.
>
> bash-2.05b$ cal 09 1752
>    September 1752
> Su Mo Tu We Th Fr Sa
>        1  2 14 15 16
> 17 18 19 20 21 22 23
> 24 25 26 27 28 29 30
>
> bash-2.05b$ uname -a
> FreeBSD jester 4.7-RELEASE FreeBSD 4.7-RELEASE #10: Sat Nov 16 16:00:07
> EST 2002     root@jester:/usr/obj/usr/src/sys/JESTER  i386

So cal is using English dates, but it isn't consistent because it
doesn't know about the change in the new year date in England in 1751/2.

I got my data from
http://serendipity.magnet.ch/hermetic/cal_stud/cal_art.htm
which also talks about proleptic calendars; that is, the current
calendar is projected backwards (or the Julian calendar is projected
forwards), so that there are no breaks in the sequence of dates.

Does the SQL standard define what calendar it is using?  My copy of Date
& Cannan: 'SQL -- The Standard Handbook' says:

"YEAR - Valid values: 0001 to 9999.  This permits the specification of
dates prior to the invention of the Gregorian calendar and assumes that
the rules of the Gregorian calendar can be applied retrospectively."

So I suggest we should use the Gregorian proleptic calendar as the
default.

Example:    England       Italy         Proleptic
                                        Gregorian

          15 Sep 1752   15 Sep 1752    15 Sep 1752
           1 Sep 1752   12 Sep 1752    12 Sep 1752
          25 Mar 1751    5 Apr 1751     5 Apr 1751
          24 Mar 1750    4 Apr 1751     4 Apr 1751
           6 Oct 1582   15 Oct 1582    15 Oct 1582
           5 Oct 1582    5 Oct 1582    14 Oct 1582
          24 Mar 1581   24 Mar 1582     3 Apr 1582

Admittedly, nowhere in the world would have used the proleptic Gregorian
date before 15 Oct 1582 (except for all of the third century AD in the
Roman empire), but it has the virtue of consistency, and any system that
changes the calendar basis at a particular date will also be wrong for a
large number of users.  Even if we say that most of our users are from
England and its colonies, that is not to say that their uses for
historical dates are limited to events in those places or seen from the
point of view of their then inhabitants.  It is really only historians,
genealogists and astronomers who are likely to want to use such ancient
dates, so we ought to consider their needs before fixing the type.
Consider how you would record the marriage date of an Italian marrying a
Briton in the seventeenth century, if you wanted to relate it to events
in both countries.

If the date type were able to have subtypes, the typmod field could be
used to determine what system a date belonged to and therefore how it
should be represented, or else these could be predefined function
parameters.  E.g.:

           0  Proleptic Gregorian - default
           1  Astronomical
           2  England and colonies
           4  Italy, Spain, France, Portugal
           5  Catholic Germany
           6  Protestant Germany
           7  Sweden
           8  Russia
           9  Greece
          10  Turkey
          11  Eastern Orthodox Church
          12  ab urbe condita
          13  Jewish
          ... etc ...

While the conversions for these could be written to be based on John's
current proposal, it would be purer and simpler if they did not have to
cope with any gaps at all.  We don't have to define all of them, but
make the structure so that they can be filled in later.

The internal effect would be that the earliest possible date would be
about 29th November 4714BC (Gregorian proleptic calendar); since that
exceeds the SQL limit, it should not be a problem.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "That at the name of Jesus every knee should bow, of
      things in heaven, and things in earth, and things
      under the earth; And that every tongue should confess
      that Jesus Christ is Lord, to the glory of God the
      Father."              Philippians 2:10,11