Forcing a max year instead of throwing ValueError

[ying-w]

I would like the behavior prior to this change #1307

I think this might be possible by registering an adapter to handle large dates. However, how would the comparison operator work in this situation (does anyone have sample code).

I have a postgres db where I have user input dates which often have some crazy values and I'd like to enforce a max of 9999-12-31 when moving over to python instead of failing on fetch

[dvarrazzo]

What do you need how comparison operator work?

If you compare in postgres, comparison works according to postgres rules. If you compare in Python, comparison works according to Python rules. You can register your own adapter and customize the treatment of overflowing dates any way you wish; comparison will depend on where you do it and is unrelated.

[ying-w]

@dvarrazzo do you have code pointer to an adapter that compares in postgres? from the few adapter examples i was looking at, i assume i need to parse strings in python to infer if year is too large.

was mostly wondering if there was a better way / more obvious way to do this

[dvarrazzo]

If you can make a comparison in postgres you may as well sanitize the value returning them within an expected range:

SELECT ... min(date_field, '9999-12-31'::date) AS date_field ...

If you want to use a custom adapter, you can make one that returns datetime.max in case of overflow. The example is for the timestamptz; you can do something similar for timestamp and date types.

import datetime as dt
import psycopg2
from psycopg2.extensions import PYDATETIMETZ, PYDATETIME, PYDATE

def cast_safe_tstz(data, cur):
    try:
        return PYDATETIMETZ(data, cur)
    except ValueError:
        return dt.datetime.max

SAFE_TSTZ = psycopg2.extensions.new_type(PYDATETIMETZ.values, "SAFE_TSTZ", cast_safe_tstz)
psycopg2.extensions.register_type(SAFE_TSTZ)

conn = psycopg2.connect("")
cur = conn.cursor()
cur.execute("select '12345-12-31'::timestamptz")
cur.fetchone()[0]
# datetime.datetime(9999, 12, 31, 23, 59, 59, 999999)

The details are explained in the docs.