Fix: can't subtract offset-naive and offset-aware datetimes

TL;DR#

If you're seeing TypeError: can't subtract offset-naive and offset-aware datetimes, the cause is usually a naive datetime (no timezone info) being subtracted from an aware one (with timezone info). Fix it by making both datetimes aware before subtraction — typically by attaching UTC to the naive object with datetime.now(timezone.utc) or dt.replace(tzinfo=timezone.utc).

If that doesn't work, scroll to verify the fix — there are two common variants this guide also covers.

The error, decoded#

You pull a timestamptz column from PostgreSQL, then try to compute its age:

from datetime import datetime
import psycopg2
 
conn = psycopg2.connect("dbname=test user=postgres")
cur = conn.cursor()
cur.execute("SELECT created_at FROM orders LIMIT 1;")
row = cur.fetchone()
db_time = row[0]          # aware datetime (e.g., 2026-09-11 14:30:00+00:00)
now = datetime.now()      # naive datetime (no tzinfo)
age = now - db_time       # 💥 TypeError

The interpreter throws:

TypeError: can't subtract offset-naive and offset-aware datetimes

This error appears whenever you mix a datetime that knows its UTC offset (aware) with one that doesn’t (naive). It’s not a PostgreSQL problem — it’s a Python datetime constraint. The same error fires with Django ORM, SQLAlchemy, or any library that returns aware datetimes from a timezone-aware column.

The original report on Stack Overflow (527 upvotes, 533k views) shows the exact scenario: a timestamptz field from PostgreSQL and datetime.datetime.now() — which returns a naive local time — collide during subtraction.

Why Python doesn’t auto-align timezones#

Python’s datetime module distinguishes between two kinds of objects:

• Offset-naive: tzinfo is None. The datetime represents a wall-clock time without any reference to UTC. datetime.now() and datetime.utcnow() both produce naive objects — the former in local time, the latter in UTC but still without a tzinfo attribute.
• Offset-aware: tzinfo is a concrete tzinfo subclass (e.g., datetime.timezone.utc, a pytz timezone, or a zoneinfo.ZoneInfo). The datetime carries an explicit UTC offset, so arithmetic like subtraction can be performed unambiguously.

The subtraction operator - calls __sub__, which checks that both operands have the same awareness state. If one is naive and the other aware, Python raises TypeError because the result would be ambiguous — you can’t know whether the naive datetime is meant to be interpreted as UTC, local time, or something else.

The root cause in the PostgreSQL scenario is that psycopg2 (and most database adapters) automatically converts TIMESTAMP WITH TIME ZONE columns to aware datetime objects, while datetime.now() returns a naive one. The mismatch is immediate.

The relevant code path that triggers the error is inside CPython’s datetime C implementation, but the check is effectively:

if (self.tzinfo is None) != (other.tzinfo is None):
    raise TypeError("can't subtract offset-naive and offset-aware datetimes")

The fix: aligning awareness before subtraction#

The solution is to make both operands either aware or naive before you subtract. The accepted answer on the Stack Overflow question suggests stripping the timezone from the aware datetime with .replace(tzinfo=None), but the modern, safer approach is to add timezone information to the naive datetime so that all datetimes in your application are UTC-aware.

Step 1 — Inspect tzinfo attributes#

Before fixing, confirm the awareness state of each datetime:

print(db_time.tzinfo)   # <UTC> or <DstTzInfo 'UTC' UTC+00:00>
print(now.tzinfo)       # None

If one is None and the other is not, you’ve found the mismatch.

Step 2 — Make the naive datetime aware (recommended)#

Use datetime.now(timezone.utc) to get the current time as an aware UTC datetime. This works with the built-in datetime.timezone:

from datetime import datetime, timezone
 
now_aware = datetime.now(timezone.utc)
age = now_aware - db_time   # works, both are aware

If you’re on Python 3.9+, you can use zoneinfo for IANA timezone support:

from zoneinfo import ZoneInfo
from datetime import datetime
 
now_aware = datetime.now(ZoneInfo("UTC"))

For older Python versions or when you need to attach a timezone to an existing naive datetime that already represents UTC, use pytz:

import pytz
from datetime import datetime
 
naive_utc = datetime.utcnow()          # naive, but represents UTC
aware_utc = pytz.utc.localize(naive_utc)
age = aware_utc - db_time

Important: Never use dt.replace(tzinfo=pytz.utc) with pytz for a naive datetime that isn’t already in UTC, because replace doesn’t adjust the wall-clock time — it just slaps on the timezone, which can lead to incorrect offsets during DST transitions. For UTC, replace(tzinfo=timezone.utc) is safe because UTC has no DST.

Step 3 — Convert the aware datetime to naive (when appropriate)#

If you’re certain that all datetimes in your application should be naive (e.g., you store everything as UTC without timezone in the database), you can strip the timezone from the database value:

naive_db_time = db_time.replace(tzinfo=None)
age = datetime.utcnow() - naive_db_time

This approach is simpler but loses the timezone context. It’s acceptable if your entire codebase consistently treats all datetimes as UTC and you never need to convert to other timezones.

PostgreSQL / psycopg2-specific handling#

When you query a TIMESTAMP WITH TIME ZONE column, psycopg2 returns an aware datetime with a psycopg2.tz.FixedOffsetTimezone or a datetime.timezone depending on the server’s TimeZone setting. If your PostgreSQL server is set to UTC, the returned datetime will have tzinfo=datetime.timezone.utc.

To avoid the mismatch at the source, you can configure psycopg2 to return naive datetimes by setting the cursor_factory or using a connection-level type caster, but that’s rarely recommended. Instead, always produce aware datetimes in your Python code.

If you’re struggling with PostgreSQL connection issues before you even get to datetime handling, check out Fix: password authentication failed for user "postgres" or Fix: psql: command not found (Install PostgreSQL Client) to get your environment sorted.

Common scenarios — Django, SQLAlchemy, parsed strings#

The error isn’t limited to raw psycopg2. Here are two patterns that still trip up developers:

Django ORM#

Django’s USE_TZ = True setting makes the ORM return aware datetimes. If you then compare them with datetime.now() (naive), you’ll hit the same TypeError. The fix is to use Django’s timezone utility:

from django.utils import timezone
 
now = timezone.now()          # aware if USE_TZ=True
age = now - obj.created_at    # works

If you’re using Supabase and encounter a “Database Error Saving New User” trigger issue, timezone mismatches can also surface there — see Supabase "Database Error Saving New User" Trigger Fix for a related PostgreSQL troubleshooting walkthrough.

Parsed strings without timezone#

When you parse a datetime string that lacks a timezone offset, datetime.strptime returns a naive datetime. If you later subtract it from an aware one, the error appears. Always attach a timezone after parsing:

from datetime import datetime, timezone
 
naive = datetime.strptime("2026-09-11 14:30:00", "%Y-%m-%d %H:%M:%S")
aware = naive.replace(tzinfo=timezone.utc)   # assume the string is UTC

Verify the fix#

After applying the alignment, run a quick test:

from datetime import datetime, timezone
 
# Simulate an aware datetime from the database
db_time = datetime(2026, 9, 11, 14, 30, tzinfo=timezone.utc)
now_aware = datetime.now(timezone.utc)
delta = now_aware - db_time
print(delta)          # e.g., 0:05:23.456789
print(type(delta))    # <class 'datetime.timedelta'>

No TypeError should appear. If you still see the error, double-check that both operands have a non‑None tzinfo (or both have None). Print repr() of each datetime to inspect the exact tzinfo object.

Best practices — canonical UTC and consistency#

To prevent this error from returning, adopt a single rule: every datetime inside your application is UTC-aware. Use datetime.now(timezone.utc) everywhere, never datetime.now() or datetime.utcnow(). When storing in PostgreSQL, use TIMESTAMP WITH TIME ZONE (which stores UTC internally) and let the adapter handle conversion.

For additional PostgreSQL reliability, you might also want to review Postgres INSERT If Not Exists: Fix Duplicate Key Violations to avoid another common pitfall when inserting time-series data.

FAQ#

What does "offset-naive and offset-aware" mean in Python datetime?#

An offset-naive datetime has no timezone information (tzinfo is None). An offset-aware datetime carries a tzinfo object that defines its UTC offset. Python forbids arithmetic between the two because the result would be ambiguous.

How do I make a naive datetime timezone-aware in Python?#

Use datetime.now(timezone.utc) or datetime.now(ZoneInfo("UTC")) in 3.9+. For existing naive datetimes that represent UTC, call dt.replace(tzinfo=timezone.utc). With pytz, use pytz.utc.localize(naive_dt) — never replace with a pytz timezone unless the datetime is already in that zone’s local time.

Why does datetime.utcnow() still cause the error?#

datetime.utcnow() returns a naive datetime (no tzinfo) even though it represents UTC. Subtracting it from an aware datetime triggers the same TypeError. Always use datetime.now(timezone.utc) instead.

Should I store datetimes as UTC or with timezone in my database?#

Store them as TIMESTAMP WITH TIME ZONE (which normalizes to UTC internally) and let your application work with aware UTC datetimes. This avoids ambiguity and makes timezone conversions straightforward.

Related#

• Fix: password authentication failed for user "postgres"
• Fix: psql: command not found (Install PostgreSQL Client)
• Supabase "Database Error Saving New User" Trigger Fix
• Postgres INSERT If Not Exists: Fix Duplicate Key Violations