Understanding Date‑Range Queries in SQL: Between, Timestamp Precision and Code Readability

Yesterday my company's WeChat public account posted an article about how to retrieve all orders from a single day using SQL, and I discussed the problem with colleagues over lunch.

The original query

SELECT order_no FROM orders WHERE create_time >= '2015-04-07 00:00:00' AND create_time <= '2015-04-07 23:59:59'

can miss orders that fall exactly on the last second because create_time is a Timestamp type, leading to hidden bugs during reconciliation.

MySQL experts recommend writing the range as

SELECT order_no FROM orders WHERE create_time >= '2015-04-07 00:00:00' AND create_time < '2015-04-08 00:00:00'

, which avoids the off‑by‑one issue.

One colleague suggested appending .999 to the upper bound (e.g., create_time <= '2015-04-07 23:59:59.999') and setting the timestamp precision to milliseconds. While this works for newer MySQL versions (5.6 supports microseconds), it introduces a “magic number” that harms code maintainability.

We also debated the semantics of BETWEEN ... AND .... After checking the MySQL documentation, we learned that it represents a closed‑closed interval, which can be confusing for developers who are not aware of this detail.

Because BETWEEN can be less explicit, I argued that using explicit comparisons (

create_time >= '2015-04-07 00:00:00' AND create_time < '2015-04-08 00:00:00'

) makes the intent clearer, especially for future maintainers.

The discussion then drifted to JUnit's assert methods, where I was unsure whether the first argument is actual or expected. The confusion highlighted how APIs that rely on memorized argument order can cause mistakes, reinforcing the need for self‑explanatory code.

Overall, the piece stresses that developers should write code that is obviously correct, even before performance concerns, and that clear, unambiguous SQL (and test) statements reduce hidden bugs and make maintenance easier.