Thread: 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 67 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!
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
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
"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
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
> 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
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!
[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.