PostgreSQL BETWEEN operator behaving differently

user_6998444 0
Viewed 297

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 11th 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 31st 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?

between postgresql sql
4 Answers

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';
user_18632290

If created_timestamp is a date/time value, rather than just a date, then everything on June 11 after midnight (00:00 hours) will be outside the BETWEEN. So you can do this maybe

date_trunc('day', created_timestamp) BETWEEN '2019-05-31' AND '2019-06-11'

As already pointed out the dates should be yyyy-mm-dd format. They don't need to be cast to date in this case as Gordon mentioned, but he's right in that in some situations you do need to cast to date and it doesn't hurt to do so.

user_116061930

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.

user_64643080

Presumably, created_timestamp is actually in a date/time format. So, compare to date/time values, not strings:

created_timestamp >= '2019-05-21'::date and
created_timestamp < '2019-06-11'::date

Note that I changed the between to two inequalities. That means that the codes behaves as expected when created_timestamp actually has a time component.

user_11440350
Related