It Worked on Every Environment Except Prod: Getting Daily Reports Right Across Timezones

Every day, a report runs: "what did each store sell yesterday?" One of the stores is in Lagos, the other in London. The report showed both stores as having zero sales for the last two hours of the day: wrong numbers, every day, in production. Here's the conversion pattern that fixed it.

---

The Problem

You store your timestamps in UTC. Django recommends it, Postgres defaults to it, and it keeps your data unambiguous. Order 582 was placed at 2025-03-10 23:15:00+00:00. Clean, portable, no ambiguity.

But a report that asks for "Tuesday's orders" needs to know what Tuesday means. Tuesday in Lagos starts at 11 PM Monday UTC. Tuesday in London starts at midnight UTC. If your reporting query uses UTC midnight for everyone, orders placed between 11 PM and midnight UTC get assigned to the wrong day for every store east of GMT.

The fix is deceptively simple: each store knows its timezone, and the reporting query converts the store's local day-bounds into a UTC range before filtering. But there's a landmine in the conversion, and it's one that only fires in production:

>>> import pytz, datetime
>>> store_tz = pytz.timezone("Africa/Lagos")
>>> start = datetime.datetime(2025, 3, 10, 0, 0, 0)
>>> store_tz.localize(start)
datetime.datetime(2025, 3, 10, 0, 0, tzinfo=<DstTzInfo 'Africa/Lagos' WAT+1:00:00 STD>)

That worked. But then the code runs again, maybe from a Celery retry, maybe from a helper that already called make_aware, and the datetime is already offset-aware:

>>> aware_start = store_tz.localize(start)     # fine
>>> store_tz.localize(aware_start)             # BOOM
ValueError: Not naive datetime (tzinfo is already set)

The same line fails because pytz.localize() refuses to touch an already-aware datetime. This didn't surface in development because the call path never produced an aware datetime in the dev environment, but in production, with re-queued tasks and retried jobs, it did.

The root cause isn't the localize() call itself: it's that an aware datetime reached the function at all. Guard the input before it hits datetime.combine(), and localize() never sees an aware value. The fix is one guard at the top of the function, and the lesson is that timezone-correct reporting needs both the right model and the right conversion hygiene.

Note on modern Django: The ValueError above is a pytz-specific quirk. Django 4.0 deprecated pytz in favour of Python's built-in zoneinfo, and Django 5.0 removed pytz support entirely. With zoneinfo you use datetime.replace(tzinfo=tz) instead of .localize(), which behaves differently. The code in this article uses pytz because the bug originated in a legacy codebase, and millions of Django apps are still in that position. But the underlying trap is the same regardless of library: passing an aware datetime that carries a UTC date into a function expecting to produce local midnight bounds will silently fetch the wrong day's data if you aren't guarding your inputs.

---

The Solution

Two pieces: a per-store timezone field, and a helper that safely turns a local calendar date into a UTC range for filtering.

Step 1: Give each store a timezone

# models.py
class Store(models.Model):
    name = models.CharField(max_length=255)
    timezone = models.CharField(
        max_length=50,
        default="Africa/Lagos",
        help_text="IANA timezone name, e.g. 'Africa/Lagos', 'Europe/London'.",
    )

One field. The Lagos store has "Africa/Lagos", the London store has "Europe/London". If a store never sets one, the default keeps it working (and wrong for anyone who notices, which is better than crashing).

Step 2: Convert local day-bounds to a UTC range

The reporting query needs a UTC start and end for the store's local day. A helper handles the conversion safely:

import pytz
from datetime import date, datetime, time, timedelta
from django.utils import timezone as dj_timezone

def local_day_bounds_utc(store_tz: str, target_date: date | datetime) -> tuple[datetime, datetime]:
    """Return (utc_start, utc_end) for the store's local calendar day."""
    tz = pytz.timezone(store_tz)

    # Normalize the date input — if an aware datetime arrives from a
    # Celery retry or pre-processed helper, extract the calendar date
    # in the store's own timezone.
    #
    # This matters because an aware datetime carries a UTC date component,
    # not a local one. A Lagos store at 11:30 PM local time is already the
    # next calendar day in UTC — passing that directly to datetime.combine()
    # would silently produce bounds for the wrong day.
    if isinstance(target_date, datetime) and not dj_timezone.is_naive(target_date):
        target_date = target_date.astimezone(tz).date()

    naive_start = datetime.combine(target_date, time.min)
    naive_end = datetime.combine(target_date + timedelta(days=1), time.min)

    aware_start = tz.localize(naive_start)
    aware_end = tz.localize(naive_end)

    return aware_start.astimezone(pytz.UTC), aware_end.astimezone(pytz.UTC)

naive_end is built as the start of the next day rather than time.max on the current day. This avoids microsecond edge cases on the upper boundary. The next day's midnight is unambiguous. For March 11 and a Lagos store, the UTC range becomes 2025-03-10 23:00+00:00 to 2025-03-11 23:00+00:00. That's Lagos-Tuesday in UTC.

Step 3: Filter with the UTC range

The reporting query now reads cleanly:

from django.db.models import Sum

store = Store.objects.get(id=store_id)
store_tz = pytz.timezone(store.timezone)
target_date = datetime.now(store_tz).date() - timedelta(days=1)  # yesterday, store-local

utc_start, utc_end = local_day_bounds_utc(store.timezone, target_date)

orders = Order.objects.filter(
    store=store,
    created_at__gte=utc_start,
    created_at__lt=utc_end,
)
totals = orders.aggregate(total=Sum("amount"))

One query, timezone-correct by construction. "Yesterday" is derived in the store's own timezone, not the server's. The function converts to UTC once, and the database does the rest.

---

Why This Works

The conversion is a pure function of data you already have. The store's timezone is a column on the store model. No API calls, no user-preference lookups, no frontend involvement. The reporting query just reads the field and does the math.

Postgres handles the UTC comparison natively. created_at is a timestamptz column, which stores everything as UTC internally. When Django passes two UTC-aware datetimes to __gte/__lt, Postgres can resolve the range with a plain index scan on the created_at column: no timezone arithmetic per row, no sequential scan. The conversion happens once per query, in Python. The database just compares two UTC values.

It works for any date, not just "yesterday." The helper takes a date, not a relative expression. Daily EOD reports, weekly summaries, arbitrary date-picker queries. All call the same function.

---

Design Decision: Put the Guard on the Input, Not the Output

The local_day_bounds_utc helper normalizes its input before doing anything else. datetime.combine() takes a date and a time. If you pass an aware datetime as the date argument, Python uses its date component, which is the UTC date, not the store-local date. A Lagos store at 11:30 PM local time on March 10 is already March 11 in UTC. Pass that aware datetime directly to combine and you silently get bounds for the wrong day, with no error raised.

In production, the input sometimes isn't a plain date. A Celery task retries and the retry wrapper has already called make_aware on the parameter. A helper upstream pre-processed the date into UTC. The isinstance + is_naive check at the top of the function catches this: if the incoming value is an aware datetime, it's converted to the store's own timezone first, and only the local calendar date is extracted. This means "yesterday" is always resolved in the store's timezone, regardless of what timezone the parameter arrived with.

The lesson: timezone correctness has two layers. The model (which timezone applies) and the conversion hygiene (is the input naive or aware, and does it carry the right date?). You need both. The second layer fails silently in every environment except the one where retries and re-queues happen.

---

The Result

The daily sales report now returns correct totals for every store, regardless of timezone. Adding a new store in a new city is one field change on the store record. The reporting query picks it up automatically. The input guard means the report produces correct results even when a date parameter arrives through an unexpected code path.

How are you handling per-store or per-tenant timezone-aware reporting? Are you doing it at the DB level, in Python, or have you found a third approach? I'd love to hear what's worked for you in the comments.

¡Hasta luego!