Convert python ordinal date to normal date in Postgresql

Question

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

Postgresql's Julian day is actually based on Midnight November 24, 4714 BC. See documentation postgresql.org/docs/8.3/static/functions-formatting.html — Wulfhart
– Wulfhart, Commented Jul 19, 2012 at 17:31

fraber · Accepted Answer · 2012-05-06 14:01:12Z

2

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.

answered May 6, 2012 at 14:01

fraber

213 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Frank Bollack · Accepted Answer · 2010-07-13 10:12:45Z

1

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

edited Jul 13, 2010 at 10:12

answered Jul 13, 2010 at 8:15

Frank Bollack

25.3k5 gold badges51 silver badges58 bronze badges

2 Comments

infinito Over a year ago

This seems to work. The only problem is using '0000-12-31' returns en error ERROR: date/time field value out of range: "0000-12-31". I have to use 0001-12-31 and the substract one year.... any idea on how to fix this?

Frank Bollack Over a year ago

this works on my PotsgreSQL 8.3 installation. What data base do you use? As an alternative use: select date (date '0000-12-31' + interval '733973 days' - interval '1 day');

Leniel Maccaferri · Accepted Answer · 2010-07-13 08:03:12Z

0

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

Collectives™ on Stack Overflow

Convert python ordinal date to normal date in Postgresql

3 Answers 3

Comments

Edit:

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Edit:

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related