Correct SQL Logic for Selecting Today's Records in PostgreSQL

In the original query

SELECT count(1) FROM odr WHERE odr.create_time >= '2012-09-03 00:00:00' AND odr.create_time <= '2012-09-03 23:59:59';

the author noticed that the upper bound 23:59:59 excludes the last second of the day, which can lead to inaccurate daily statistics.

The discussion points out that "today" should be defined mathematically as the interval from the start of the day (inclusive) to the start of the next day (exclusive). Using a closed interval that ends at 23:59:59 omits any events occurring in the final second, which may cause subtle bugs in accounting or reporting.

To fix the logic, the query should be rewritten as

odr.create_time >= '2012-09-03 00:00:00' AND odr.create_time < '2012-09-04 00:00:00'

, employing an open‑closed interval that correctly captures all rows belonging to the target date.

For PostgreSQL, two equivalent ways are shown:

Timestamp‑string form:

create_time >= '2012-12-04 00:00:00' AND create_time < '2012-12-05 00:00:00'

Symbolic form using date functions:

create_time >= current_date AND create_time < current_date + 1

The article concludes with a brief author bio, noting the writer's extensive experience in search technology, distributed systems, and database research, and that the content originates from an internal Qunar wiki.