Today I Learned: Using BETWEEN with SQL Dates

I came across some weird behavior today when running PostgreSQL queries with dates. Specifically when using BETWEEN to search for records that are within some date range. It turns out, the BETWEEN comparison predicate behaves differently for the date type as it does for the timestamp type.

Consider the following for table articles.

CREATE TABLE articles (
  ...
  created_at date
)

SELECT * FROM articles
WHERE created_at BETWEEN '2018-08-01'::DATE AND '2018-08-07'::DATE

For articles, we don’t care about the time the article was posted, only the day.

In the PostgreSQL documentation for BETWEEN, it states that it is inclusive on both ends e.g. x <= a <= y. This would mean that our query above would return all articles created between August 1, 2018 and including August 7, 2018. And it does.

Now, consider the table comments.

CREATE TABLE comments (
  ...
  created_at timestamp
)

SELECT * FROM comments
WHERE created_at BETWEEN '2018-08-01'::TIMESTAMP AND '2018-08-07'::TIMESTAMP

Very similar to articles except here we do care about the time a comment was posted, so the column type is timestamp.

Similarly, we want to retrieve all comments that were posted between August 1, 2018 and including August 7, 2018. If you expected the query above to return comments that were created on August 1, 2018 and all comments created on August 7, 2018, you would be wrong.

I learned from a colleague, “Your target column will be inclusive of that type”. This sounds a bit confusing, so let me explain.

Using BETWEEN creates a time interval. This means that '2018-08-07'::DATE is read in as 2018-08-07 00:00:00. Therefor the query is checking if the row will be included within the date type of 2018-08-07 00:00:00. And it is. '2018-08-07 00:00:00'::DATE is equal to '2018-08-07'::DATE.

However, in the query for comments, the '2018-08-07'::TIMESTAMP is also read in as 2018-08-07 00:00:00. This means that all comments created during the day of August 7, 2018 will not be included in the results. For instance, a comment created 2018-08-07 04:13:47 is not before or equal to 2018-08-07 00:00:00.

Here it is in a more digestible format.

|           |          2018-08-07 | 2018-08-07 04:13:47 | <= 2018-08-07::DATE | <= 2018-08-07::TIMESTAMP |
|-----------|---------------------|---------------------|---------------------|--------------------------|
| date      |          2018-08-07 | 2018-08-07          | true                | true                     |
| timestamp | 2018-08-07 00:00:00 | 2018-08-07 04:13:47 | true                | false                    |