PostgreSQL BETWEEN operator behaving differently

Question

I was using a sql query to pull a couple of days' data of a deployed solution

And I was using the query in following manner

select column1, column2, column3, column 4 
where <condition 1> AND <condition 2> AND 
      created_timestamp BETWEEN '05-31-2019' AND '06-11-2019'

Now it used to fetch the values of all the columns and was filtering the data by the conditions as expected, except there was one twist here. It was not fetching data for the date 11^th of June which is being provided as the upper limit.

Now as far as I know between operator includes both the endpoints of the range being provided.

What confused me more was that it was somehow including the data for the date 31^st of may which is the upper limit.

1. I need to know whether or not between operator(especially in postgresql don't think so it will change just want to confirm) includes end points of the range?

2. Even if it includes/excludes it should behave same for both the end points I do not understand the biased behavior any one having a clue on this?

Answer 1

Try using proper date literals:

SELECT column1, column2, column3, column4
FROM your_table
WHERE created_timestamp BETWEEN '2019-05-31' AND '2019-06-11';

2019-05-31 is in the ISO 8601 format, and unambiguously means May 31, 2019 in any mode.

You may read more about the rules for date/time types here.

Edit:

The reason why data from 11-June are not showing up is that using 2011-06-11 as the upper bound in the range is the same as using 2011-06-11 00:00:00. That is, it only includes 11-June exactly at midnight. To mitigate this, use 12-June as the upper bound:

SELECT column1, column2, column3, column4
FROM your_table
WHERE created_timestamp >= '2019-05-31' AND created_timestamp < '2019-06-12';

Answer 2

I assume that created_timestamp is a timestamp (with or without time zone).

The literal you used, 06-11-2019, corresponds to midnight on that date:

SELECT '06-11-2019'::timestamp with time zone;
      timestamptz       
------------------------
 2019-06-11 00:00:00+02
(1 row)

So it is not surprising that the results don't include the data from June 11th.

There are two ways forward:

1. Use the next day and the < operator:

   ... WHERE created_timestamp >= '05-31-2019' AND created_timestamp < '06-12-2019'
   

2. convert the timestamp to a date:

   ... WHERE date(created_timestamp) BETWEEN '05-31-2019' AND '06-11-2019'
   

The second option cannot use an index on created_timestamp (but it can use an index on date(created_timestamp)).

Your query depends on the PostgreSQL setting of DateStyle, so make sure that that setting is always the way you need it.