"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?

---------------------------------------------------------------------------

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 dual;
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?

davidl=# SELECT to_char(timestamp '1970-01-01 00:00:00 GMT','J SSSS MS');
to_char
---------------
2440588 0 000
(1 row)

____________________________________________________________________________________
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. +