From: | David Lee Lambert <dlambert(at)bmtcarhaul(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | "Julian day" date format is off by 12 hours |
Date: | 2006-12-08 22:46:42 |
Message-ID: | 200612081746.42303.dlambert@bmtcarhaul.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jim Nasby | 2006-12-09 02:25:02 | Re: BUG #2802: Feature request: tinyint and unsigned types |
Previous Message | Brien R. Givens | 2006-12-08 18:51:53 | BUG #2818: ADO Field.Attributes reports NULL on NOT NULL fields |