What are the common pitfalls with dates, times and time zones in SQL?
What are the common pitfalls with dates, times and time zones in SQL?
Most date bugs come from range boundaries, hidden time parts and time zones.
BETWEEN is inclusive on both ends, so it mishandles timestamps:
-- misses 2026-03-31 10:00 because '2026-03-31' means midnight
WHERE created_at BETWEEN '2026-03-01' AND '2026-03-31'
-- correct: start inclusive, end exclusive
WHERE created_at >= '2026-03-01' AND created_at < '2026-04-01'
SQL Server datetime rounds 23:59:59.999 up to the next day, which is another reason to avoid "end of day" literals.
WHERE DATE(created_at) = '2026-03-15' -- function on column: index not used
WHERE created_at >= '2026-03-15' AND created_at < '2026-03-16' -- index friendly
timestamptz stores an absolute instant and converts to the session TimeZone; timestamp (without zone) stores a wall-clock value with no zone meaning.Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.