PostgreSQL Pro Tip: Beware of BETWEEN

Bill Babeaux - Jan 10 '20 - - Dev Community

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'
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

You can avoid this problem by writing the query:

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

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.

. .