Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #25973 > unrolled thread

How to represent dates BC

Back to article view | Back to comp.lang.python

Contents

  How to represent dates BC Laszlo Nagy <gandalf@shopzeus.com> - 2012-07-24 11:55 +0200
    Re: How to represent dates BC jwp <james.pye@gmail.com> - 2012-07-28 16:25 -0700
    Re: How to represent dates BC jwp <james.pye@gmail.com> - 2012-07-28 16:25 -0700

#25973 — How to represent dates BC

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

[toc] | [next] | [standalone]

#26170

On Tuesday, July 24, 2012 2:55:29 AM UTC-7, Laszlo Nagy wrote:
>  >>> conn.getqueryvalue("select '1311-03-14 BC'::date")
> What is the good representation here? Should I implement my own date 

Do your datetime formatting in postgres: "select '1311-03-14 BC'::date::text"

PG does have a have a reasonable set of functions for working with datetime.
If you need something more complicated than a simple cast to text, I'd suggest creating a function:

CREATE OR REPLACE FUNCTION fmt_my_dates(date) RETURNS text LANGUAGE SQL AS
$$
SELECT EXTRACT(.. FROM $1)::text || ...
$$;

[toc] | [prev] | [next] | [standalone]

#26171

On Tuesday, July 24, 2012 2:55:29 AM UTC-7, Laszlo Nagy wrote:
>  >>> conn.getqueryvalue("select '1311-03-14 BC'::date")
> What is the good representation here? Should I implement my own date 

Do your datetime formatting in postgres: "select '1311-03-14 BC'::date::text"

PG does have a have a reasonable set of functions for working with datetime.
If you need something more complicated than a simple cast to text, I'd suggest creating a function:

CREATE OR REPLACE FUNCTION fmt_my_dates(date) RETURNS text LANGUAGE SQL AS
$$
SELECT EXTRACT(.. FROM $1)::text || ...
$$;

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.python

csiph-web