Amey-Thakur SQL date and time
Time bugs are quiet: a report is off by a day for some users, a range misses the last hour, a daily job double counts around a clock change. Nearly all of them come from an implicit time zone or a boundary written with BETWEEN.
Method
- Store instants in UTC with a time zone aware type. A naive timestamp is ambiguous the moment two regions use it, and the ambiguity is unrecoverable later.
- Convert for display and grouping only. A daily report means days in someone's local zone, so convert at the point of bucketing and record which zone was used.
- Write half-open ranges. Greater than or equal to the start and strictly less than the end. BETWEEN on timestamps either misses the final fraction of a second or double counts a boundary row.
- Beware date arithmetic across clock changes. Adding a day is not always adding twenty-four hours where daylight saving applies, and the two operations are different functions.
- Keep the filter column bare. Wrapping the column in a function usually prevents index use, so transform the bounds instead of the column (see sql-optimization).
- Separate event time from ingestion time. When something happened and when you recorded it are different questions, and analytics eventually needs both (see batch-vs-streaming).
Boundaries
- Time zone rules change by legislation, so correctness depends on an up-to-date zone database rather than arithmetic.
- Date-only values are not instants; treating a birth date as a timestamp introduces a zone question that should not exist.
- Precision differs between engines and can silently truncate on insert.