How to represent dates BC

[Laszlo Nagy]

import datetimeTraceback (most recent call last):


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

 

[jwp]

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 || ...
$$;

 

[jwp]

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 || ...
$$;