Re: timestamp patch to extend legal range of dates.

From: Oliver Elphick <olly(at)lfix(dot)co(dot)uk>
To: Rod Taylor <rbt(at)rbt(dot)ca>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, John Cochran <jdc(at)fiawol(dot)org>, PostgreSQL Patches <pgsql-patches(at)postgresql(dot)org>
Subject: Re: timestamp patch to extend legal range of dates.
Date: 2003-02-04 22:20:16
Message-ID: 1044397215.5690.313.camel@linda.lfix.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)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(dot)Elphick(at)lfix(dot)co(dot)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

In response to

Responses

Browse pgsql-patches by date

  From Date Subject
Next Message Sean Chittenden 2003-02-04 23:21:35 Adding // comments (ANSI??)...
Previous Message Rod Taylor 2003-02-04 16:09:20 Re: timestamp patch to extend legal range of dates.