How to handle BC (Before Christ) dates between PostgreSQL & Python via psycopg?-CodePudding

I understand that PostgreSQL has a larger date range allowing it to store BC dates, whereas Python goes to minimum year 1. What is a good way to insert/select such dates from PostgreSQL using psycopg? I'm wondering if I should just store the year as a smallint (signed 2 byte int) in a separate column & "date&month" as a separate column with a fixed year, say 2000? Any ideas would be appreciated, thank you.

Below is the code & error btw:

import psycopg

with psycopg.connect(f"dbname=mydb user=mydb host=xx password=xx") as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM mytable;")
        print(cur.fetchall())

# Error asf:
---------------------------------------------------------------------------
DataError                                 Traceback (most recent call last)
Cell In [4], line 4
    2 with conn.cursor() as cur:
    3     cur.execute("SELECT * from event;")
----> 4     print(cur.fetchall())

File D:\My Drive\proj\venv\lib\site-packages\psycopg\cursor.py:851, in Cursor.fetchall(self)
    849 self._check_result_for_fetch()
    850 assert self.pgresult
--> 851 records = self._tx.load_rows(self._pos, self.pgresult.ntuples, self._make_row)
    852 self._pos = self.pgresult.ntuples
    853 return records

File psycopg_binary\\_psycopg/transform.pyx:467, in psycopg_binary._psycopg.Transformer.load_rows()

File psycopg_binary\\types/datetime.pyx:382, in psycopg_binary._psycopg.DateLoader.cload()

File psycopg_binary\\types/datetime.pyx:376, in psycopg_binary._psycopg.DateLoader._error_date()

DataError: date too small (before year 1): '0100-01-01 BC'

The Database Table looks as under:

mydb=# SELECT id, date, descr FROM mytable ORDER BY date;
   id |     date      |     description
------ --------------- ---------------------
    3 | 4000-10-01 BC | long time ago
    1 | 0170-10-01 BC | after it
    2 | 2000-02-02    | newwer times 
    4 | 4000-10-01    | future times
(4 rows)

CodePudding user response：

Considering that in python the smallest year number allowed in a date or datetime object.MINYEAR is 1 and the largest year number allowed in a date or datetime object.MAXYEAR is 9999.

And in PostgreSQL timestamp data type range goes from 4713 BC to 294276 AD with a 1 microsecond resolution, then all dates and date operations out of python's range should be processed in the database itself, and passed as strings:

with conn.cursor() as cur:
        cur.execute("select id,to_char(date,'yyyy-mm-dd BC'),descr from mytable order by date;")
        print(cur.fetchall())