Thread: Date/Time Types : internals
Hi, At the bottom of the page about Date/Time types ( http://www.postgresql.org/docs/9.1/interactive/datatype-datetime.html ) there is this sentence : Date conventions before the 19th century make for interesting reading, but are not consistent enough to warrant coding into a date/time handler. This sentence seemed very strange to me, and I am not sure to really understand what it implies (or not) for the user. Could someone explain that this really means and implies? Thank you, Florence Cousin.
Florence Cousin <cousinflo@free.fr> wrote: > At the bottom of the page about Date/Time types ( > http://www.postgresql.org/docs/9.1/interactive/datatype-datetime.html > ) > there is this sentence : > > Date conventions before the 19th century make for interesting > reading, but are not consistent enough to warrant coding into a > date/time handler. > > > This sentence seemed very strange to me, and I am not sure to > really understand what it implies (or not) for the user. Could > someone explain that this really means and implies? You can get some idea by reading this page, especially the "Adoption" section: http://en.wikipedia.org/wiki/Gregorian_calendar I guess the point is that for hundreds of years, the same day could have a different date depending which country's calendar you were looking at. I'm not entirely clear why there's a problem if you pick the Gregorian calendar and apply it retroactively. If George Washington was able to adapt to his birthday changing, I think I could deal with it, too: http://www.archives.gov/legislative/features/washington/ II mean, there are still a lot of other calendars in use today, and we don't let that stop us from using the Gregorian calendar. -Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> I guess the point is that for hundreds of years, the same day could
> have a different date depending which country's calendar you were
> looking at. I'm not entirely clear why there's a problem if you
> pick the Gregorian calendar and apply it retroactively.
Which is, in fact, exactly what our code does. I think that bit in the
docs is trying to explain why we do that rather than try to get the
code to reflect what people really used back then.
A possibly comparable point is that for timezone info we use the Olsen
database (tzdata), which *does* make an effort to reflect historical
realities. In consequence, at least once every several months we
get somebody complaining about what a strange GMT offset he's seeing
for timestamps before 1900 or so. If there's anyone out there who
actually likes that behavior, we've not heard about it. (Not that
I am going to try to get Olsen et al to change their policy.)
regards, tom lane
Le 18/04/2012 22:14, Tom Lane a écrit : > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: >> I guess the point is that for hundreds of years, the same day could >> have a different date depending which country's calendar you were >> looking at. I'm not entirely clear why there's a problem if you >> pick the Gregorian calendar and apply it retroactively. > Which is, in fact, exactly what our code does. I think that bit in the > docs is trying to explain why we do that rather than try to get the > code to reflect what people really used back then. OK, thank you both, that is clear for me know. But maybe this sentence, without further explanations, is just a bit confusing for a simple user, who will ask himself what to do with this remark. If I understand it well, it has no consequences at all, except that dates from 4713 BC are correctly handled by PostgreSQL. Or maybe this sentence lacks only some linking words to explain more clearly that the point is that PostgreSQL chose the Julian calendar instead of other date conventions, because the other date conventions are inconsistent before 1900. Sorry, I am not fluent enougn in english to propose a patch. > > A possibly comparable point is that for timezone info we use the Olsen > database (tzdata), which *does* make an effort to reflect historical > realities. In consequence, at least once every several months we > get somebody complaining about what a strange GMT offset he's seeing > for timestamps before 1900 or so. If there's anyone out there who > actually likes that behavior, we've not heard about it. (Not that > I am going to try to get Olsen et al to change their policy.) > > regards, tom lane > Maybe this could be worth a small explanation somewhere on this page? (I mean, explaining that before 1900, date and time can be inconsistent, and lead to GMT offset that are not round).
On Wed, Apr 18, 2012 at 4:14 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: >> I guess the point is that for hundreds of years, the same day could >> have a different date depending which country's calendar you were >> looking at. I'm not entirely clear why there's a problem if you >> pick the Gregorian calendar and apply it retroactively. > > Which is, in fact, exactly what our code does. I think that bit in the > docs is trying to explain why we do that rather than try to get the > code to reflect what people really used back then. What I find a bit confusing is that this part talks about the Julian calendar, but elsewhere: <para> The SQL standard states that <quote>Within the definition of a <quote>datetime literal</quote>, the <quote>datetime value</quote>s are constrained by the natural rules for dates and times according to the Gregorian calendar</quote>. Dates between 1582-10-05 and 1582-10-14, although eliminated in some countries by Papal fiat, conform to <quote>natural rules</quote> and are hence valid dates. <productname>PostgreSQL</> follows the SQL standard's lead by counting dates exclusively in the Gregorian calendar, even for years before that calendar was in use. </para> So which calendar are we using, Julian or Gregorian? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> So which calendar are we using, Julian or Gregorian?
Gregorian. I fail to see anyplace that suggests we use the Julian
calendar. We do understand the Julian date system, which is something
entirely different with a confusingly similar name. (Julian dates are
just a count of days since a reference point in 4713 BC.)
regards, tom lane
I wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> So which calendar are we using, Julian or Gregorian?
> Gregorian. I fail to see anyplace that suggests we use the Julian
> calendar. We do understand the Julian date system, which is something
> entirely different with a confusingly similar name. (Julian dates are
> just a count of days since a reference point in 4713 BC.)
On further reflection I wonder whether we shouldn't just get rid of
"8.5.6. Internals", which is at best rather inappropriately placed.
A person reading this portion of the docs probably couldn't care less
whether we use Julian-date calculations internally. It might be
appropriate to mention that in appendix B.4, where there's an actual
explanation of what Julian dates are; but not here.
What I think we should have here is an externally-facing specification,
that is state that we follow the Gregorian calendar rules even for years
before Pope Gregory's edict, and then reference B.4 for more info.
regards, tom lane