| Store | Cart

How to represent dates BC

From: Laszlo Nagy <gand...@shopzeus.com>
Tue, 24 Jul 2012 11:55:29 +0200
 >>> import datetime>>> old_date = datetime.date(1,12,31)>>> str(old_date)
 >>> 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.


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




Recent Messages in this Thread
Laszlo Nagy Jul 24, 2012 09:55 am
Christian Heimes Jul 24, 2012 10:29 am
Laszlo Nagy Jul 24, 2012 11:52 am
Messages in this thread