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.