Date ranges, month ends, and time boundaries

Problem and reasoning

Do not end a monthly filter at 23:59:59. A column may store milliseconds or microseconds and may be affected by time-zone conversion. A more stable range is created_at >= the month start and created_at < the next month start.

Wrapping the date column in DATE_FORMAT, DATE, or YEAR inside WHERE can prevent ordinary index use. A range predicate applied directly to the source column is usually clearer and easier to optimize.

Applications must also define storage and display time zones. A daily report across zones needs an explicit user, business, or UTC boundary.

Examples

Sample table

Table：orders

orders
id	customer_id	product_id	name	department	amount	status	created_at	discount
1	101	201	Alice	Sales	1200	paid	2026-01-03	0
2	102	202	Bob	IT	900	pending	2026-01-08	50
3	101	201	Cindy	Sales	1500	paid	2026-02-02	100
4	104	203	David	HR	700	cancelled	2026-02-15	0

Before / Not recommended

SELECT * FROM orders WHERE DATE_FORMAT(created_at, '%Y-%m') = '2026-02' ORDER BY created_at;

Before result

result
id	customer_id	product_id	name	department	amount	status	created_at	discount
3	101	201	Cindy	Sales	1500	paid	2026-02-02	100
4	104	203	David	HR	700	cancelled	2026-02-15	0

After / Recommended

SELECT * FROM orders WHERE created_at >= '2026-02-01' AND created_at < '2026-03-01' ORDER BY created_at;

After result

result
id	customer_id	product_id	name	department	amount	status	created_at	discount
3	101	201	Cindy	Sales	1500	paid	2026-02-02	100
4	104	203	David	HR	700	cancelled	2026-02-15	0

Why it is better：The half-open range covers every February timestamp precision and applies predicates directly to created_at.

Was this explanation clear?

Keep learning

Continue with related topics or compare database syntax in the SQL dialect translator.