Level 3 · Orders from March 2024

Date filters are everywhere in analytics: last month, this quarter, year to date. They are also where a lot of quietly wrong numbers come from.

Dates vs timestamps

signup_date in customers is a DATE: just a day, like 2024-03-31. order_date in orders is a TIMESTAMP: a day plus a time, like 2024-03-31 18:42:10. When you compare a timestamp to a date literal such as '2024-03-31', the literal is treated as midnight at the start of that day, 2024-03-31 00:00:00.

The BETWEEN trap

BETWEEN a AND b means >= a AND <= b. It looks perfect for “March”:

-- bug: misses almost all of March 31
WHERE order_date BETWEEN '2024-03-01' AND '2024-03-31'

The upper bound is 2024-03-31 00:00:00, so an order at 9am on March 31 is excluded. People “fix” this with '2024-03-31 23:59:59', which still misses anything in the last second and breaks when timestamps have milliseconds.

The half-open range

The standard analyst pattern is: on or after the first moment, strictly before the first moment of the next period.

WHERE order_date >= '2024-03-01'
  AND order_date <  '2024-04-01'

It works for dates and timestamps, never double-counts a boundary when you stack consecutive months, and lets the database use an index on order_date.

You will also see WHERE CAST(order_date AS DATE) BETWEEN '2024-03-01' AND '2024-03-31' or WHERE DATE_TRUNC('month', order_date) = '2024-03-01'. Both return the right rows, but wrapping the column in a function usually prevents index use, which matters on big tables in Postgres and MySQL. Prefer the half-open range.

Dialect notes

• Postgres and DuckDB accept '2024-03-01' and convert it automatically. You can be explicit with DATE '2024-03-01' or TIMESTAMP '2024-03-01 00:00:00'.
• BigQuery separates DATE, DATETIME and TIMESTAMP strictly and may need TIMESTAMP('2024-03-01').
• MySQL accepts the quoted string the same way as Postgres.
• Time zones: Harbor & Pine stores timestamps without a time zone. In real warehouses, check whether “March” means UTC or the business’s local time. It changes the numbers at the edges.

Common mistakes

• BETWEEN on timestamps, as above.
• Using <= '2024-03-31' on a timestamp, which is the same bug in a different form.
• Writing dates in a regional format like '03/01/2024'. Always use ISO YYYY-MM-DD.