RFC3339 timezone offsets break SQLite timestamp comparisons #1

New issue

Open

opened 2026-08-07 14:22:02 +00:00 by hum3 · 1 comment

hum3 commented

2026-08-07 14:22:02 +00:00

Owner

The ncruces/go-sqlite3 driver stores time.Time as RFC3339 text, preserving timezone offsets (e.g. "2026-06-16T00:30:00+01:00" for BST). SQLite's <= / >= operators do string comparison on TEXT columns, which gives wrong results when stored and queried timestamps have different timezone offsets.

Example

Stored: "2026-06-16T00:30:00+01:00" (00:30 BST = 23:30 UTC June 15)
Query param: "2026-06-15T23:59:59.999999999Z" (end of June 15 UTC)
String comparison: "2026-06-16..." > "2026-06-15..." → movement excluded
Correct result: 23:30 UTC IS before 23:59 UTC → movement should be included

This affects any query using value_time <= ? or similar timestamp comparisons when the Go application mixes timezones.

Possible approaches

Could be split to preserve formatting and allow SQL queries:

Normalize to UTC on write: In BindTime or the pglike wrapper, convert all time.Time values to UTC before storing. This ensures string comparison works correctly. Downside: loses original timezone info.
Store both: Store the RFC3339 string for display/formatting, plus a separate UTC-normalized column or a numeric (Unix) representation for comparisons. This preserves formatting while enabling correct queries.
Document the limitation: Note that users must pass UTC times for correct SQL comparisons with the pglike driver.

Discovered in

go-luca issue #1 — CalculateDailyInterest returns 0 when movements are recorded in a non-UTC timezone.

Migrated from Codeberg: originally #1, opened 2026-03-16.

The ncruces/go-sqlite3 driver stores `time.Time` as RFC3339 text, preserving timezone offsets (e.g. `"2026-06-16T00:30:00+01:00"` for BST). SQLite's `<=` / `>=` operators do string comparison on TEXT columns, which gives wrong results when stored and queried timestamps have different timezone offsets. ## Example - Stored: `"2026-06-16T00:30:00+01:00"` (00:30 BST = 23:30 UTC June 15) - Query param: `"2026-06-15T23:59:59.999999999Z"` (end of June 15 UTC) - String comparison: `"2026-06-16..." > "2026-06-15..."` → movement excluded - Correct result: 23:30 UTC IS before 23:59 UTC → movement should be included This affects any query using `value_time <= ?` or similar timestamp comparisons when the Go application mixes timezones. ## Possible approaches Could be split to preserve formatting and allow SQL queries: 1. **Normalize to UTC on write**: In `BindTime` or the pglike wrapper, convert all `time.Time` values to UTC before storing. This ensures string comparison works correctly. Downside: loses original timezone info. 2. **Store both**: Store the RFC3339 string for display/formatting, plus a separate UTC-normalized column or a numeric (Unix) representation for comparisons. This preserves formatting while enabling correct queries. 3. **Document the limitation**: Note that users must pass UTC times for correct SQL comparisons with the pglike driver. ## Discovered in go-luca issue #1 — `CalculateDailyInterest` returns 0 when movements are recorded in a non-UTC timezone. --- *Migrated from Codeberg: originally [#1](https://codeberg.org/hum3/go-postgres/issues/1), opened 2026-03-16.*

hum3 commented

2026-08-07 14:22:03 +00:00

Author

Owner

This aligns with option 2 above: store UTC for correct SQL comparisons, and optionally keep the original timezone offset as metadata for formatting. The pglike driver could normalise to UTC on write via time.Time.UTC() before calling BindTime, making SQL queries correct by default.

(hum3, 2026-03-16)

Additional thought on the approach: you could assume timezone 0 (UTC) for storage and SQL queries. The timezone then becomes purely a display/formatting concern — a way of presenting the output to the user, not part of the stored data. This aligns with option 2 above: store UTC for correct SQL comparisons, and optionally keep the original timezone offset as metadata for formatting. The pglike driver could normalise to UTC on write via `time.Time.UTC()` before calling `BindTime`, making SQL queries correct by default. *(hum3, 2026-03-16)*

No labels

OnDemand

No milestone

No project

No assignees

1 participant

Notifications

Due date

The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set

Reference

hum3/go-postgres#1

No description provided.

Rows
Columns