You could use the function
age() to simplify your expression (returns
interval). But it's much more efficient to use a sargable expression to begin with.
This operates with the exact time difference (current time is relevant):
WHERE status = 'PENDING_PAYMENT'
AND status_updated_at < now() - interval '10 days'
To operate with whole calendar days (up to but excluding midnight, local time zone):
AND status_updated_at < CURRENT_DATE - 10
You can just subtract
date, the result is still a
date which can be compared directly to the data type
timestamp of your column.
You seem to be asking for the behavior of the second variant. The difference is more than 10 days.
How does the evaluation work?
In reply to your comment.
CURRENT_DATE is a special function returning a
date (implemented internally with
('now'::cstring)::date in pg 9.4). There are many variants of the operator
-. I find 43 in the system catalog
pg_operator in my current test DB. The right one is picked by means of the operands' data types.
date - integer, the
integer amount in days is subtracted from the
date - just because it's defined that way. It makes sense for me, the unit of a
date is "days". Modern Postgres stores dates and timestamps as
integer numbers internally. It's very cheap to transform one into the other and it's very cheap to add/subtract days to/from a
date and it's also very cheap to compare them.
The result of the expression is a
status_updated_at is a
timestamp column. I was assuming an implicit assignment cast at first, but that step is not even necessary,
timestamp share a binary compatible format and can be compared directly. There is variant of the
< operator registered for
timestamp < date. Column values can be tested "as is", the expression is sargable, a btree index can be used.
SELECT oprname, oprleft::regtype, oprright::regtype
WHERE oprname = '<'
AND oprleft = 'timestamp'::regtype;
timestamptz, the matter of time zones would be added to the equation, which is complex but typically burns down to very simple evaluation:
It would be ideal if
now() was not calculated for each record and
instead be a single timestamp value calculated at the beginning.
Your wish has been granted. The
now() family of functions (including
CURRENT_DATE id defined
now() returns the same value within the same transaction.