Do to a bug in pgtypeslin I've been experimenting with the day of week
stuff a little bit and I found that we do not agree with the output of
the cal command on older dates. I have no idea which one is correct:

mm=# select to_char('1000/01/01'::date,'D');
 to_char
 ---------
 4
(1 row)

Thus we believe 1000/1/1 is a Wednesday. cal says:

    Januar 1000
So Mo Di Mi Do Fr Sa
    1  2  3  4  5  6
 7  8  9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31

Thus 1000/1/1 is a Monday. Is this a bug in cal? Or do we produce the
wrong output? Or do I simply misread the output?

Michael
-- 
Michael Meskes
Email: Michael at Fam-Meskes dot De
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

Sun, 5 Oct 2003 13:06:41 +0200
Michael Meskes <meskes@postgresql.org>
Day of week question
Michael Meskes writes:

> mm=# select to_char('1000/01/01'::date,'D');
>  to_char
>  ---------
>  4
> (1 row)
>
> Thus we believe 1000/1/1 is a Wednesday. cal says:

> Thus 1000/1/1 is a Monday. Is this a bug in cal? Or do we produce the
> wrong output? Or do I simply misread the output?

cal takes into account the change from the Julian to the Gregorian
calendar.  PostgreSQL assumes that the Gregorian calendar is valid for all
times in the past.  So both answers are correct given those assumptions.

-- 
Peter Eisentraut   peter_e@gmx.net


Sun, 5 Oct 2003 13:59:26 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Day of week question

Looks like it is caused by the switch to the Gregorian calendar in 1752,
when 11 days were chopped out of September ( in England and America -
elsewhere anywhere between Oct 1582 and early 20th century). A quick scan of
the code didn't show postgres taking account of this oddity, but I might
have missed something.

cheers

andrew


----- Original Message ----- 
From: "Michael Meskes" <meskes@postgresql.org>
To: "PostgreSQL Hacker" <pgsql-hackers@postgresql.org>
Sent: Sunday, October 05, 2003 7:06 AM
Subject: [HACKERS] Day of week question


> Do to a bug in pgtypeslin I've been experimenting with the day of week
> stuff a little bit and I found that we do not agree with the output of
> the cal command on older dates. I have no idea which one is correct:
>
> mm=# select to_char('1000/01/01'::date,'D');
>  to_char
>  ---------
>  4
> (1 row)
>
> Thus we believe 1000/1/1 is a Wednesday. cal says:
>
>     Januar 1000
> So Mo Di Mi Do Fr Sa
>     1  2  3  4  5  6
>  7  8  9 10 11 12 13
> 14 15 16 17 18 19 20
> 21 22 23 24 25 26 27
> 28 29 30 31
>
> Thus 1000/1/1 is a Monday. Is this a bug in cal? Or do we produce the
> wrong output? Or do I simply misread the output?
>
> Michael
> -- 
> Michael Meskes
> Email: Michael at Fam-Meskes dot De
> ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org
> Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster


Sun, 5 Oct 2003 08:03:27 -0400
"Andrew Dunstan" <andrew@dunslane.net>
Re: Day of week question
"Andrew Dunstan" <andrew@dunslane.net> writes:
> Looks like it is caused by the switch to the Gregorian calendar in 1752,
> when 11 days were chopped out of September ( in England and America -
> elsewhere anywhere between Oct 1582 and early 20th century).

There was some discussion awhile back about extending PG's date code
to know about the Julian calendar, but the idea pretty much died when
it was pointed out that you'd need locale-specific information about
exactly when the switchover occurred.

SQL99 makes it perfectly clear that all datetime values are Gregorian,
for example we find wording like this in the <literal> section:

         9) If <date value> is specified, then it is interpreted as a date
            in the Gregorian calendar.

So one could argue that the existing PG behavior is SQL-compliant.
I tend to regard this as an easy out, but nonetheless it's an available
defense if someone tries to beat you up about PG's "wrong answers".

			regards, tom lane

Sun, 05 Oct 2003 12:00:25 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Day of week question 


Tom Lane wrote:

>"Andrew Dunstan" <andrew@dunslane.net> writes:
>  
>
>>Looks like it is caused by the switch to the Gregorian calendar in 1752,
>>when 11 days were chopped out of September ( in England and America -
>>elsewhere anywhere between Oct 1582 and early 20th century).
>>    
>>
>
>There was some discussion awhile back about extending PG's date code
>to know about the Julian calendar, but the idea pretty much died when
>it was pointed out that you'd need locale-specific information about
>exactly when the switchover occurred.
>
>SQL99 makes it perfectly clear that all datetime values are Gregorian,
>for example we find wording like this in the <literal> section:
>
>         9) If <date value> is specified, then it is interpreted as a date
>            in the Gregorian calendar.
>
>So one could argue that the existing PG behavior is SQL-compliant.
>I tend to regard this as an easy out, but nonetheless it's an available
>defense if someone tries to beat you up about PG's "wrong answers".
>  
>
Perhaps we need a function or two to convert pre-gregorian dates to 
gregorian dates and vice versa, with the cutover date either a 
configuration variable (default the 1752 date) or a parameter of the 
function.

e.g. j_to_g('1700-01-01'::date) => '1700-01-13'
       j_to_g('1800-01-01'::date) => '1800-01-01'

just a thought

andrew


Sun, 05 Oct 2003 17:51:47 -0400
Andrew Dunstan <andrew@dunslane.net>
Re: Day of week question

> Do to a bug in pgtypeslin I've been experimenting with the day of week
> stuff a little bit and I found that we do not agree with the output of
> the cal command on older dates. I have no idea which one is correct:
> 
> mm=# select to_char('1000/01/01'::date,'D');
>  to_char
>  ---------
>  4
> (1 row)
> 
> Thus we believe 1000/1/1 is a Wednesday. cal says:
> 
>     Januar 1000
> So Mo Di Mi Do Fr Sa
>     1  2  3  4  5  6
>  7  8  9 10 11 12 13
> 14 15 16 17 18 19 20
> 21 22 23 24 25 26 27
> 28 29 30 31
> 
> Thus 1000/1/1 is a Monday. Is this a bug in cal? Or do we produce the
> wrong output? Or do I simply misread the output?

What do we do for September 1752?

bash-2.05a$ cal 9 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



Mon, 06 Oct 2003 09:41:52 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Day of week question
On Sun, Oct 05, 2003 at 05:51:47PM -0400, Andrew Dunstan wrote:
> Perhaps we need a function or two to convert pre-gregorian dates to 
> gregorian dates and vice versa, with the cutover date either a 
> configuration variable (default the 1752 date) or a parameter of the 

I guess that's one of the problems. :-)

Why shall 1752 be the default date? The original introduction of
gregorian dates after all was in 1582. And some parts of the earth
didn't switch before the 20th century. So, as pointed out, we'd need a
locale dependant default.

Michael
-- 
Michael Meskes
Email: Michael at Fam-Meskes dot De
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

Mon, 6 Oct 2003 09:28:00 +0200
Michael Meskes <meskes@postgresql.org>
Re: Day of week question
[sNip]
> Why shall 1752 be the default date? The original introduction of
> gregorian dates after all was in 1582. And some parts of the earth
> didn't switch before the 20th century. So, as pointed out, we'd need a
> locale dependant default.

    	Since a database can be serving someone in a different locale from the 
local one, this should be configurable at the SQL statement level.

    	It would probably also be good to have a system-wide configurable 
default in one of the configuration files to satisfy the needs of those who 
only need to use one system and wish to avoid the overhead of using non-
standard SQL statements.

-- 
Randolf Richardson - rr@8x.ca
Vancouver, British Columbia, Canada

Please do not eMail me directly when responding
to my postings in the newsgroups.

Sat, 29 Nov 2003 05:54:56 +0000 (UTC)
Randolf Richardson <rr@8x.ca>
Re: Day of week question