INTERVAL and period bounds.

INTERVAL is a duration: INTERVAL '1 month', INTERVAL '14 days'. DATE '2024-02-01' + INTERVAL '1 month' gives the start of the next month (2024-03-01 00:00:00). The result is a TIMESTAMP, but comparing it with a DATE works.

The safest way to describe a period is a half-open interval "from inclusive, to exclusive": order_date >= DATE '2024-02-01' AND order_date < DATE '2024-03-01'. February 2024 keeps all its days (the 29th included) and 1 March stays out. Do not write "up to 29 February" with <= or BETWEEN: with a TIMESTAMP column an order late on 29 February would be lost, and the day count changes in leap years.

You can derive the upper bound from the lower one with INTERVAL: < DATE '2024-02-01' + INTERVAL '1 month'. Always spell the bounds out in the condition.

Common mistakes: <= the last day instead of < the next first day; BETWEEN with an end day; INTERVAL without quotes ('1 month'); checking only one bound.

Small example:

SELECT COUNT(*) AS orders_count FROM orders WHERE order_date >= DATE '2024-01-01' AND order_date < DATE '2024-01-01' + INTERVAL '1 month';