SQL dates — half-open ranges and the midnight trap

Start with the basics

Date and time logic looks like arithmetic, but it is full of boundaries — the edge of a day, a month, a week, a time zone — and each boundary is a place a report can quietly gain or lose rows.

The safest habit is the half-open range: include the start, and stop just before the next start. 'June' means from June 1 up to but not including July 1.

That one rule covers most of the traps. The rest come from truncating dates, time zones, and month math that clamps at the end of the month.

One that works

SELECT COUNT(*) AS june
FROM events
WHERE ended >= TIMESTAMP '2024-06-01'
  AND ended <  TIMESTAMP '2024-07-01';

The half-open range keeps all of June 30 and excludes July 1 — one June event.

A timestamp range should be half-open — include the start, stop before the next start — so no day is cut short or double-counted.

When to use it

• ·Reach for date filters whenever you report 'per day', 'this month', or 'in the last 30 days'.
• ·Use a half-open range for any timestamp window: >= the start and < the next start.
• ·Fill missing days with a calendar table when a report must be gap-free.

That habit — stop before the next start — is exactly what BETWEEN gets wrong on timestamps. Its upper bound lands on midnight, so the last day's afternoon falls outside the range. Here is the day that bites.

See it happen

The month that loses its last afternoon — BETWEEN on timestamps

You want June's sessions. One session ended at 2pm on June 30. Watch BETWEEN quietly drop it, because its upper bound stops at midnight.

SELECT id FROM sessions
WHERE ended BETWEEN TIMESTAMP '2024-06-01'
  AND TIMESTAMP '2024-06-30'
ORDER BY id;

SELECT id FROM sessions
WHERE ended >= TIMESTAMP '2024-06-01'
  AND ended <  TIMESTAMP '2024-07-01'
ORDER BY id;

! The June report drops every session that ended after midnight on the 30th — BETWEEN's closed upper bound cuts the last day short.

The rest of the family

More ways date logic goes quietly wrong

Self-check

How to spot it in your own SQL

• →A monthly total is slightly low and the last day looks short.
• →You filtered a timestamp range with BETWEEN.
• →A 'daily' metric shifts depending on the time zone.
• →A report is missing the days that had no data.

What people miss

Date logic reads like plain arithmetic, so the boundary bugs hide in plain sight — a report that quietly ends at midnight, a month that clamps, a time zone that shifts the day. Nothing errors; the numbers are just a little wrong at the edges.

Go deeper