Path: csiph.com!usenet.pasdenom.info!aioe.org!news.stack.nl!newsfeed.xs4all.nl!newsfeed5.news.xs4all.nl!xs4all!post.news.xs4all.nl!not-for-mail
Return-Path: <gandalf@shopzeus.com>
X-Original-To: python-list@python.org
Delivered-To: python-list@mail.python.org
X-Spam-Status: OK 0.004
X-Spam-Evidence: '*H*': 0.99; '*S*': 0.00; 'converts': 0.07; 'postgresql': 0.07; 'valueerror:': 0.07; 'subject:How': 0.09; 'python': 0.09; 'events.': 0.09; 'here?': 0.09; 'objects.': 0.09; "wouldn't": 0.11; 'conn': 0.16; 'storing': 0.16; 'subject:dates': 0.16; 'driver': 0.17; 'thanks,': 0.18; '>>>': 0.18; 'import': 0.21; '"",': 0.22; 'header:User-Agent:1': 0.26; 'select': 0.26; 'values': 0.26; '(most': 0.27; 'field,': 0.30; 'implement': 0.32; 'file': 0.32; 'could': 0.32; 'dates': 0.33; 'like:': 0.33; 'traceback': 0.33; 'handle': 0.33; 'problem': 0.33; 'to:addr :python-list': 0.33; 'skip:d 20': 0.34; 'text': 0.34; 'stores': 0.35; "won't": 0.35; 'something': 0.35; 'really': 0.36; 'but': 0.36; 'url:org': 0.36; 'should': 0.36; 'itself': 0.37; 'data': 0.37; 'store': 0.38; 'object': 0.38; 'url:docs': 0.38; 'to:addr:python.org': 0.39; 'received:192': 0.39; 'application': 0.40; 'received:192.168': 0.40; 'range': 0.60; 'capable': 0.63; 'more': 0.63; 'received:204': 0.72; 'day': 0.73
Date: Tue, 24 Jul 2012 11:55:29 +0200
From: Laszlo Nagy <gandalf@shopzeus.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:14.0) Gecko/20120714 Thunderbird/14.0
MIME-Version: 1.0
To: python-list@python.org
Subject: How to represent dates BC
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-BeenThere: python-list@python.org
X-Mailman-Version: 2.1.12
Precedence: list
List-Id: General discussion list for the Python programming language <python-list.python.org>
List-Unsubscribe: <http://mail.python.org/mailman/options/python-list>, <mailto:python-list-request@python.org?subject=unsubscribe>
List-Archive: <http://mail.python.org/pipermail/python-list>
List-Post: <mailto:python-list@python.org>
List-Help: <mailto:python-list-request@python.org?subject=help>
List-Subscribe: <http://mail.python.org/mailman/listinfo/python-list>, <mailto:python-list-request@python.org?subject=subscribe>
Newsgroups: comp.lang.python
Message-ID: <mailman.2525.1343123740.4697.python-list@python.org>
Lines: 51
NNTP-Posting-Host: 2001:888:2000:d::a6
X-Trace: 1343123740 news.xs4all.nl 6937 [2001:888:2000:d::a6]:33087
X-Complaints-To: abuse@xs4all.nl
Xref: csiph.com comp.lang.python:25973

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