Julian date to normal date

Question

Hello,

Is there any way to convert a 6-digit julian date to a normal timestamp in Postgres? Date is created using python's datetime.toordinal().

For example,

>>> 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!

Answer 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

Answer 2

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