Interval (days) in PostgreSQL with two parameters

Question

In this query:

Select * from table where future_date - CURRENT_DATE <= '10';

It returns the interval of 10 days or less.

How to add two parameters? Example:

Select * from table where future_date - CURRENT_DATE <= '10' AND >= '30';

I already tried:

Select * from table where future_date - CURRENT_DATE BETWEEN '10' AND '30'

But it doesn't work.

Solution 1

SELECT  *
FROM    mytable
WHERE   future_date BETWEEN CURRENT_DATE + '10 days'::INTERVAL AND CURRENT_DATE + '30 days'::INTERVAL

Solution 2

Explanation

Your first example is syntactical nonsense, but your second is valid and would (mostly) work as expected if your column was actually a date like the column name suggests:

Select * from table where future_date - CURRENT_DATE BETWEEN '10' AND '30'

The result of the expression future_date - CURRENT_DATE depends on the actual, undisclosed data type of future_date.

If future_date is a date, the result of future_date - CURRENT_DATE is an integer number signifying the difference in days, and your untyped string literals '10' and '30' are cast to integer per assignment cast: the query is valid (even if inefficient) and covers a range of 21 days (not 20).

If future_date is a timestamp (or timestamptz), the result is an interval signifying a time interval. It would be odd but legal to use CURRENT_DATE in this context. The value is coerced to timestamp (or timestamptz) assuming 00:00 hours of the day.

However, your untyped string literals are now cast to interval and without any given time unit, an integer number means seconds by default, so the predicate effectively selects a narrow time frame of 10 to 30 seconds from now.

See for yourself:

SELECT '10'::interval

interval
---------
'00:00:10'

To clarify misinformation: CURRENT_DATE is just fine. It's a standard SQL function without parentheses for legacy reasons. Used to be implemented as now()::date internally in Postgres. Both are STABLE functions (so "runtime constants"). The additional problem with your expression: it's very inefficient because it's not sargable.

Proper solution

For a date column:

SELECT *
FROM   mytable
WHERE  future_date BETWEEN CURRENT_DATE + 10
                       AND CURRENT_DATE + 30;  -- 21 days (!)

You can just add integer to date to add / subtract days.

This gives you a range of 21 (not 20!) days because BETWEEN includes lower and upper bound. Typically, you'd want to include the lower but exclude the upper bound.

For a timestamp or timestamptz column:

SELECT *
FROM   mytable
WHERE  future_date >= now() + interval '10 days'
AND    future_date <  now() + interval '30 days';  -- 20 days (!)

This covers a time frame of 20 days (!) spread out across 21 calendar days (!) unless you start at midnight exactly, in which case exactly 20 calendar days are covered completely.

Typically, you'd want to work with calendar days as bounds:

... 
WHERE  future_date >= (CURRENT_DATE + 10)
AND    future_date <  (CURRENT_DATE + 30);

Or either of these expressions for timestamp or timestamptz:

now()::date               + interval '10 days'  -- returns timestamp
CURRENT_DATE              + interval '10 days'  -- returns timestamp
date_trunc('day', now())  + interval '10 days'  -- returns timestamptz

The data type is cast to the the type of future_date, so it works for either type.

Note that a date is defined by its time zone. So these expressions depend on the current timezone setting of the session.

It should be evident by now, why BETWEEN .. AND .. is typically wrong with timestamps. Mostly, you want to include the lower bound and exclude the upper. BETWEEN .. AND .. would include 00:00 of the next day in the last example, thus opening a corner case for a 21st day.

Related:

• Subtract hours from the now() function
• Ignoring time zones altogether in Rails and PostgreSQL