Convert python ordinal to plain date in Postgresql

Question

Is there a way to convert a 6-digit ordinal date (days from January 1, 0001 AD) into a regular timestamp in Postgres? A date is created using python datetime.toordinal ().

For instance,

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

I need to convert "733973" to "2010-07-20" in Postgres.

Thanks!

Answer 1

Here is what occurred to me:

 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 does not use date 0001-01-01 , because datetime.toordinal() is defined as 1 for this date, therefore, the offset is -1. A.

From python docs

date.fromordinal (ordinal)
Return the date corresponding to the pro-reptic Gregorian order, where on January 1 of year 1 has serial number 1 ...

Edit:

If the date 0000-31-12 not recognized as a valid date, you can easily change the instruction 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))); 

Answer 2

In PostgreSQL 8.4, I use:

 to_date(2455452::text, 'J') 

"0000-12-31" :: the trick date above worked with PG 8.3, but it throws the indicated error with PG 8.4 (and higher, I think). PG 8.4 also requires that both to_date arguments (text, text) have type text, which is the reason for entering Julian date values ​​in the text.

Answer 3

How 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); 

Learn more about the j2date function ...

PostgreSQL Binary Label Functions

UDF project and its implementation in C for PostgreSQL