Home Original page

How to represent dates BC

Laszlo Nagy gandalf at shopzeus.com
Tue Jul 24 05:55:29 EDT 2012 
 >>> import datetime
 >>> old_date = datetime.date(1,12,31)
 >>> str(old_date)
'0001-12-31'
 >>> one_year = datetime.timedelta(days=365)
 >>> str(one_year)
'365 days, 0:00:00'
 >>> old_date - 10*one_year
Traceback (most recent call last):
   File "<stdin>", line 1, in <module>
OverflowError: date value out of range
 >>>


My main problem is that I have an application that stores dates in a 
PostgreSQL database. The PostgreSQL date type is capable of storing 
dates from 4713 BC to 294276 AD.

http://www.postgresql.org/docs/9.2/static/datatype-datetime.html

The application itself stores historical data of events. Apparently, the 
Python datetime.date object cannot handle dates before 1 AD. The 
psycopg2 driver converts date values to date objects. But not in this case:

 >>> conn = dbpool.borrow("central")
 >>> conn.getqueryvalue("select '1311-03-14 BC'::date")
Traceback (most recent call last):
   File "<stdin>", line 1, in <module>
     .... (some more tracelog here).....
     data = cur.fetchone()
ValueError: year is out of range
 >>>

What is the good solution? I could - in theory - store the dates in a 
text field, but then I won't be able to create incides on dates, 
add/substract with other date values etc.

I could try to always use something like:

select extract(year from date_field) as year,extract(month from 
date_field) as month,extract(day from date_field) as day ....

but this is really messy!

What is the good representation here? Should I implement my own date 
type? (I wouldn't want to.)

Thanks,

   Laszlo
More information about the Python-list mailing list