How to Return Postgres Dates and Intervals as JSON in Flask

The error

An endpoint that worked yesterday now returns 500 Internal Server Error. The traceback in the log ends like this:

TypeError: Object of type timedelta is not JSON serializable

The code did not change - the schema did. Someone added date and time columns to the table, including a computed duration column of type INTERVAL, and the route returns the rows as they are:

@app.get("/events")
def list_events():
    cur = conn.cursor(cursor_factory=RealDictCursor)
    cur.execute(
        "SELECT id, title, starts_at, ends_at, duration, created_at FROM events"
    )
    return jsonify({"events": cur.fetchall()})

Why it happens

JSON has strings, numbers, booleans, null, arrays and objects. It has no date type and no duration type. psycopg2 maps Postgres types to Python types:

Postgres type	Python type from psycopg2
TIMESTAMP / TIMESTAMPTZ	datetime.datetime
DATE	datetime.date
TIME	datetime.time
INTERVAL	datetime.timedelta
NUMERIC	decimal.Decimal

Flask's default JSON provider knows how to turn datetime and date into a string, but it uses the HTTP date format (Tue, 03 Mar 2026 10:00:00 GMT), which is hard to parse and always converts the value to GMT. It has no rule for timedelta, so jsonify raises a TypeError for every row that has a duration. Python's own json.dumps is stricter: it fails on datetime too.

Option 1: convert values in Python

Write one function that turns a row into JSON-safe values, and use it in every route that returns these rows:

def serialize_event(row):
    out = dict(row)
    for key in ("starts_at", "ends_at", "created_at"):
        if out.get(key) is not None:
            out[key] = out[key].isoformat()      # 2026-03-03T10:00:00+00:00
    if out.get("duration") is not None:
        out["duration"] = out["duration"].total_seconds()   # 5400.0
    return out

@app.get("/events")
def list_events():
    cur = conn.cursor(cursor_factory=RealDictCursor)
    cur.execute(
        "SELECT id, title, starts_at, ends_at, duration, created_at "
        "FROM events ORDER BY starts_at"
    )
    events = [serialize_event(r) for r in cur.fetchall()]
    return jsonify({"events": events, "count": len(events)})

Remember the single-item route too. If GET /events/<id> returns the same columns, it fails the same way until it also calls serialize_event.

ISO 8601 (isoformat()) is the best choice for dates: JavaScript's new Date() and almost every other language parse it directly. For a duration, a number of seconds is the easiest value for a client to use.

Option 2: cast in the SQL

Postgres can do the conversion before the data reaches Python:

SELECT id, title,
       to_char(starts_at, 'YYYY-MM-DD"T"HH24:MI:SS') AS starts_at,
       duration::text                                 AS duration,
       EXTRACT(EPOCH FROM duration)                   AS duration_seconds
FROM events;

duration::text gives a string such as 01:30:00. EXTRACT(EPOCH FROM ...) gives the number of seconds. Note that EXTRACT returns a NUMERIC, which arrives in Python as Decimal - Flask's default provider encodes it, but the plain json module does not. This option keeps the Python code short, but the format rules now live in each query.

Option 3: a custom JSON provider for the whole app

If many endpoints return dates and intervals, teach Flask once (Flask 2.2 and newer):

import datetime
from flask.json.provider import DefaultJSONProvider

class AppJSONProvider(DefaultJSONProvider):
    @staticmethod
    def default(o):
        if isinstance(o, (datetime.datetime, datetime.date)):
            return o.isoformat()
        if isinstance(o, datetime.timedelta):
            return o.total_seconds()
        return DefaultJSONProvider.default(o)

app.json = AppJSONProvider(app)

Now every jsonify call uses ISO 8601 for dates and seconds for intervals. Unknown types still fall through to the default behaviour, which raises a TypeError - that is what you want, because a silent str() on any object can leak strange values into your API.

Confirm the change

curl -s -o /dev/null -w "%{http_code}\n" localhost:8000/events
# 200
curl -s localhost:8000/events | python3 -m json.tool | head -20

Check that starts_at looks like 2026-03-03T10:00:00 and duration is a number or a string, not an error.

Choose a format and document it

Whatever you pick, use the same format on every endpoint and write it in the API docs. Clients break when one endpoint returns ISO dates and another returns HTTP dates, or when a duration is seconds in one place and HH:MM:SS in another.