How to Use BETWEEN Correctly in PostgreSQL

Be careful when using BETWEEN with timestamps. You might end up chopping off a whole day of data 😬

Imagine you were chief safety inspector at a local trampoline park (bonus points if that is your job in real life). You might write a query like this to get a report of accidents in December:

SELECT *
FROM accidents
WHERE created_at BETWEEN '2019-12-01' AND '2019-12-31'

Looks good, right? Nope.

This query would omit any mishaps the whole day of December 31. Why? Your query only looks from midnight on Dec 1 to midnight on Dec 31. Any bump, abrasion, or mid-air collision that occurred after midnight on the 31st won't be in your results. The query above is the same as:

SELECT *
FROM accidents
WHERE created_at >= '2019-12-01 00:00:00.000000'
AND created_at <= '2019-12-31 00:00:00.000000'

You can avoid this problem by writing the query:

SELECT *
FROM accidents
WHERE created_at >= '2019-12-01'
AND created_at < '2020-01-01'

The lesson: save BETWEEN for discrete quantities like integers. And stay away from trampoline parks. We’ve seen the data and it doesn't look pretty.

database icon
SQL editing that just rocks
PopSQL and PostgreSQL, better together