Обсуждение: "Julian day" date format is off by 12 hours
Postgres version: 8.0.6 Operating system: Ubuntu GNU/Linux I executed the following query while trying to build some date-conversion functions for data that was represented as milliseconds since the Unix epoch: davidl=# SELECT to_char(timestamp '1970-01-01 00:00:00 GMT','J SSSS MS'); to_char --------------- 2440588 0 000 (1 row) However, Postgres's notion of a "Julian Day" does not match the generally-accepted definition. According to the generally-accepted definition, the result of the query above should be 2440587 43200 000 ; that is, 12 hours past noon on Julian day 2440687, which started at noon on December 31st, 1969, GMT. I'm not sure if this should be regarded as a database bug or a documentation bug. Table 9-21 in the manual only says that a Julian day is "days since January 1, 4712 BC", so Postgres is consistent with the manual; but every other definition of a Julian day I've found says that it starts at noon. The Wikipedia article has several good references: http://en.wikipedia.org/wiki/Julian_day -- Software Developer, Precision Motor Transport Group, LLC Work phone 517-349-3011 x215 Cell phone 586-873-8813
Since to_char() is supposed to be Oracle-compatible, would someone test this query in Oracle? --------------------------------------------------------------------------- David Lee Lambert wrote: > Postgres version: 8.0.6 > Operating system: Ubuntu GNU/Linux > > I executed the following query while trying to build some date-conversion > functions for data that was represented as milliseconds since the Unix epoch: > > davidl=# SELECT to_char(timestamp '1970-01-01 00:00:00 GMT','J SSSS MS'); > to_char > --------------- > 2440588 0 000 > (1 row) > > However, Postgres's notion of a "Julian Day" does not match the > generally-accepted definition. According to the generally-accepted > definition, the result of the query above should be > > 2440587 43200 000 > > ; that is, 12 hours past noon on Julian day 2440687, which started at noon > on December 31st, 1969, GMT. > > I'm not sure if this should be regarded as a database bug or a documentation > bug. Table 9-21 in the manual only says that a Julian day is "days since > January 1, 4712 BC", so Postgres is consistent with the manual; but every > other definition of a Julian day I've found says that it starts at noon. > > The Wikipedia article has several good references: > > http://en.wikipedia.org/wiki/Julian_day > > -- > > Software Developer, Precision Motor Transport Group, LLC > Work phone 517-349-3011 x215 > Cell phone 586-873-8813 > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Oracle (9.2.0.7) doesn't seem to like the date format string... SQL> SELECT to_char(timestamp '1970-01-01 00:00:00 GMT','J SSSS MS') from d= ual; SELECT to_char(timestamp '1970-01-01 00:00:00 GMT','J SSSS MS') from dual * ERROR at line 1: ORA-01821: date format not recognized Regards, Shelby Cain ----- Original Message ---- From: Bruce Momjian <bruce@momjian.us> To: David Lee Lambert <dlambert@bmtcarhaul.com> Cc: pgsql-bugs@postgresql.org Sent: Saturday, February 3, 2007 5:56:00 PM Subject: Re: [BUGS] "Julian day" date format is off by 12 hours Since to_char() is supposed to be Oracle-compatible, would someone test this query in Oracle? >=20 > davidl=3D# SELECT to_char(timestamp '1970-01-01 00:00:00 GMT','J SSSS MS'= ); > to_char > --------------- > 2440588 0 000 > (1 row) >=20 =20 ___________________________________________________________________________= _________ Don't pick lemons. See all the new 2007 cars at Yahoo! Autos. http://autos.yahoo.com/new_cars.html
I did some research on this, and because Oracle fails with that query, we can't use them as a guide. What is happening in the code is that the "J" is independent from the "SSSS" and "MS", so you are getting a "J" based on the date (assuming midnight start/stop), and not on the actual time in the rest of the timestamp. As you stated, to do this correctly 11:59am would have a different Julian date from 12:01pm. However, I think this would make "J" much less useful because the most common use assumes midnight to 11:59pm is the same day number. What I did was to update the documentation to say explicitly "midnight": Julian Day (days since midnight, January 1, 4712 BC) I didn't document that we don't follow the specification, but the "midnight" should be a hint for those who know about it. --------------------------------------------------------------------------- bruce wrote: > > Since to_char() is supposed to be Oracle-compatible, would someone test > this query in Oracle? > > --------------------------------------------------------------------------- > > David Lee Lambert wrote: > > Postgres version: 8.0.6 > > Operating system: Ubuntu GNU/Linux > > > > I executed the following query while trying to build some date-conversion > > functions for data that was represented as milliseconds since the Unix epoch: > > > > davidl=# SELECT to_char(timestamp '1970-01-01 00:00:00 GMT','J SSSS MS'); > > to_char > > --------------- > > 2440588 0 000 > > (1 row) > > > > However, Postgres's notion of a "Julian Day" does not match the > > generally-accepted definition. According to the generally-accepted > > definition, the result of the query above should be > > > > 2440587 43200 000 > > > > ; that is, 12 hours past noon on Julian day 2440687, which started at noon > > on December 31st, 1969, GMT. > > > > I'm not sure if this should be regarded as a database bug or a documentation > > bug. Table 9-21 in the manual only says that a Julian day is "days since > > January 1, 4712 BC", so Postgres is consistent with the manual; but every > > other definition of a Julian day I've found says that it starts at noon. > > > > The Wikipedia article has several good references: > > > > http://en.wikipedia.org/wiki/Julian_day > > > > -- > > > > Software Developer, Precision Motor Transport Group, LLC > > Work phone 517-349-3011 x215 > > Cell phone 586-873-8813 > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faq > > -- > Bruce Momjian bruce@momjian.us > EnterpriseDB http://www.enterprisedb.com > > + If your life is a hard drive, Christ can be your backup. + -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +