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.
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 implies:The result of the expression
future_date - CURRENT_DATE
depends on the actual, undisclosed data type offuture_date
.If
future_date
is adate
, the result offuture_date - CURRENT_DATE
is aninteger
number signifying the difference in days, and your untyped string literals'10'
and'30'
are cast tointeger
per assignment cast: the query is valid (even if inefficient) and covers a range of 21 days (not 20).If
future_date
is atimestamp
(ortimestamptz
), the result is aninterval
signifying a time interval. It would be odd but legal to useCURRENT_DATE
in this context. The value is coerced totimestamp
(ortimestamptz
) assuming 00:00 hours of the day.However, your untyped string literals are now cast to
interval
and without any given time unit, aninteger
number defaults to meaning seconds, so the predicate effectively selects a narrow time frame of 10 to 30 seconds from now.See for yourself:
To clarify misinformation:
CURRENT_DATE
is just fine. It's a standard SQL function without parentheses for legacy reasons, implemented internally asnow()::date
in Postgres. Both areSTABLE
functions (so "runtime constants"). The additional problem with your expression: it's very inefficient because it is that is not sargable.Proper solution
For a
date
column:You can just add
integer
todate
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
ortimestamptz
column: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:
Or either of these expressions for
timestamp
ortimestamptz
: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 include00:00
of the next day in the last example, thus opening a corner case for a 21st day.Related: