Convert python ordinal date to normal date in Postgresql

Is there any way to convert a 6-digit ordinal date (days since January 1, 0001 AD) to a normal timestamp in Postgres? Date is created using python's datetime.toordinal().

For examp开发者_开发百科le,

>>> date = datetime.date(2010, 7, 20)
>>> datetime.toordinal(date)
733973

What I need is to convert '733973' back to '2010-07-20' in Postgres.

Thanks!

In PostgreSQL 8.4 I'm using:

to_date(2455452::text, 'J')

The '0000-12-31'::date trick above worked up to PG 8.3 but produces the mentioned error with PG 8.4 (and higher I believe). PG 8.4 also requires both arguments of to_date(text, text) to be of type text, that's the reason for casting the Julian date values to text.

This is what first came to my mind:

select date (date '0000-12-31' + interval '733973 days');
>> "2010-07-20"

or

select date (date '0000-12-31' + interval '1 days' * 733973);
>> "2010-07-20"

It simply adds the number of days to the date 0000-12-31. It doesn't use the date 0001-01-01, as datetime.toordinal() is defined to be 1 for this date, hence the -1 offset.

From the python docs

date.fromordinal(ordinal)
Return the date corresponding to the proleptic Gregorian ordinal, where January 1 of year 1 has ordinal 1...

Edit:

If the date 0000-31-12 is not recognised as a valid date, you can easily change the statement to something like:

select date (date '0001-01-01' + interval '733973 days' - interval '1 day');

or

select date (date '0001-01-01' + (interval '1 days' * (733973 - 1)));

What about this?

http://doxygen.postgresql.org/backend_2utils_2adt_2timestamp_8c-source.html

Look at line 01416:

j2date((int) date, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);

More on j2date function...

PostgreSQL binary timestamp functions

Project of UDF and its realization at C for PostgreSQL