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: