IST Offset Silently Doubled Our Client's Daily Spend Report

The Incident: A Client's Daily Spend Report Was Doubled Overnight

Our client runs a LiteLLM proxy in an IST (UTC+5:30) environment and relies on the /user/daily/activity/aggregated endpoint to reconcile daily spend against their cloud-provider billing. They noticed that single-day queries returned totals roughly double what their billing system reported, while multi-day queries were only slightly inflated. The discrepancy was small enough to be dismissed as rounding at first, but it broke the additivity invariant: the sum of five single-day queries exceeded the equivalent five-day aggregate.

The Client Constraint

The client's proxy receives timezone_offset=-330 from IST browsers. Their billing system reports in local IST days. The aggregation endpoint must return spend aligned to those local days, not UTC days.

What We Tried and What Failed

The Original Logic in _adjust_dates_for_timezone

The function in litellm/proxy/management_endpoints/common_daily_activity.py widened the SQL date range by a full UTC day whenever a non-zero timezone_offset_minutes was supplied. For an IST query of start=2026-05-29, end=2026-05-29, it expanded the range to start=2026-05-28, end=2026-05-29.

Why That Failed

The LiteLLM_DailyUserSpend.date column stores whole UTC days as YYYY-MM-DD with no hour-level granularity. Expanding the filter to include the adjacent UTC day pulled in 24 hours of unrelated bucket data, when only ~5.5 hours of it actually belonged to the queried local day. The result was a systematic ~100% over-count on every single-day query from IST.

The Failed Fix Attempt

We initially tried clamping the expanded range to the original start and end dates, but that reintroduced the small under-count at boundaries that the original expansion was meant to avoid.

The Working Approach

The Pass-Through Fix

The final fix made _adjust_dates_for_timezone a pass-through: when timezone_offset_minutes is non-zero, it returns the original start_date and end_date unchanged. The now-unused timedelta import was removed.

Real Commands and File Paths

The change was applied in litellm/proxy/management_endpoints/common_daily_activity.py. The SQL query in the aggregation endpoint now runs against the unexpanded date range:

SELECT date, SUM(spend) FROM LiteLLM_DailyUserSpend WHERE date >= '2026-05-29' AND date <= '2026-05-29' GROUP BY date

Verification

We seeded LiteLLM_DailyUserSpend with $100/day for five days (2026-05-29 to 2026-06-02) plus a distinct $999 row on 2026-05-28. Loaded from an IST browser (proxy receives timezone_offset=-330), the Usage tab showed exactly five bars of $100 and excluded the $999 row. The API repro confirmed the fix:

GET /user/daily/activity/aggregated?start_date=2026-05-29&end_date=2026-06-02&timezone_offset=-330
-> {"metadata":{"total_spend": 500.0, "total_api_requests": 50, ...}}

Pitfalls We Would Warn an Intern About

Timezone Offsets Are Not Date Offsets

A UTC+5:30 offset does not mean you should shift the date column by one day. The date column is a UTC-bucketed field; any conversion using only date arithmetic must round to whole UTC days, allowing up to 24h of slop per boundary.

Additivity Is a Canary

If the sum of single-day queries exceeds the multi-day aggregate, the bug is in the date-range expansion, not the aggregation logic. This invariant is the fastest way to catch silent over-counting.

Boundary Data Hides Bugs

Test with transactions at 11:55 PM and 12:05 AM in the client's local timezone. That is where timezone bugs hide, and where the over-count or under-count becomes visible.

Never Assume the Source Timezone

If the source system does not declare its timezone, do not assume. Ask. Storing timestamps without timezones is a guess that works until someone in another timezone uses your app at the wrong hour.

What We Would Do Differently Next Time

Store Hour-Level Granularity

The root cause is that LiteLLM_DailyUserSpend.date has no hour-level granularity. We would migrate the schema to store timestamps as UTC instants, enabling precise pro-rata weighting between adjacent UTC days.

Implement Pro-Rata Weighting

For IST May 29 (5.5h of UTC May 28 + 18.5h of UTC May 29), we would weight the adjacent UTC day contributions proportionally: (18.5/24) * UTC May 29 + (5.5/24) * UTC May 28. This requires schema or data changes beyond the scope of the current fix but would eliminate the boundary slop entirely.

Add Explicit Timezone Assertions

Every ingestion pipeline should have an explicit timezone assertion. If the source does not declare its timezone, the pipeline should refuse to process the data until the timezone is confirmed.

Add a Reconciliation Step

We would add a reconciliation step comparing daily aggregates between old and new systems for at least two weeks post-deployment. A matching monthly total proves little; only day-by-day reconciliation exposes the boundary shift.

Sources

This bug was tracked in the LiteLLM issue tracker and fixed in a pull request that made _adjust_dates_for_timezone a pass-through Bug: _adjust_dates_for_timezone causes ~2x over-counting on single-day queries in non-UTC timezones. Similar timezone mismatches have caused silent data shifts in Azure Databricks and Synapse pipelines The time a timezone mismatch cost us 3 days of wrong reports and in Java/Spring backends A bug in our system only appeared between midnight and 1 AM. The Azure Synapse guide covers four shapes a timezone mismatch takes and how day-by-day reconciliation exposes the boundary shift Azure Synapse Timezone Conversion: Stop Silent Offsets.

Additional context on timezone pitfalls in data engineering pipelines can be found in One of the most confusing production issues I have faced in Data Engineering and Why is my GA4 BigQuery export late?. For backfill safety patterns, see An idempotent daily backfill that's safe to run twice.

Key Takeaways

• Timezone offsets are not date offsets. Shifting a UTC date column by a timezone offset introduces whole-day slop.
• Additivity is a canary. If single-day sums exceed multi-day aggregates, the date-range expansion is wrong.
• Boundary data hides bugs. Test at 11:55 PM and 12:05 AM in the client timezone.
• Store hour-level granularity. Date-only columns cannot represent partial-day timezone shifts.
• Implement pro-rata weighting. Weight adjacent UTC days by the fraction of local hours they contain.
• Add timezone assertions. Refuse data without an explicit timezone declaration.
• Reconcile daily. Monthly totals matching proves nothing; day-by-day reconciliation catches boundary shifts.

References

• Bug: _adjust_dates_for_timezone causes ~2x over-counting on single-day queries in non-UTC timezones
• fix: remove timezone date expansion in daily-activity aggregation
• The time a timezone mismatch cost us 3 days of wrong reports
• A bug in our system only appeared between midnight and 1 AM
• Azure Synapse Timezone Conversion: Stop Silent Offsets
• One of the most confusing production issues I have faced in Data Engineering
• Why is my GA4 BigQuery export late?
• An idempotent daily backfill that's safe to run twice