6

I have a table named accounts. I am interested in retrieving those accounts whose status has not been updated since more than 10 days. I thought about this query and it seemed to work but I am not sure it's the best approach:

SELECT * from accounts
WHERE status = 'PENDING_PAYMENT'
AND (date_part('day', (now() - status_updated_at)) > 10);

One thing I don't like about this is the now() part. It would be ideal if now() was not calculated for each record and instead be a single timestamp value calculated at the beginning. Moreover, I would like this example to return 1:

Suppose that for one record the status_updated_at column has this value: 2015-10-05 23:00:00. And now() returns 2015-10-06 01:00:00. Despite being only some hours of difference I want the result to be 1.

flyer88
  • 607
  • 2
  • 6
  • 7

1 Answers1

12

You could simplify your expression with the function age() (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):

SELECT *
FROM   accounts
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;

Subtracting integer from date yields date, which can be compared to timestamp directly.

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 the current 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 for the operands' data types.

For date - integer, the integer number of days is subtracted from the date. Modern Postgres stores dates and timestamps as integer quantities internally. It's very cheap to transform one into the other, add/subtract days to/from a date, or compare them.

The result of the expression is a date, while status_updated_at is a timestamp column. I was assuming an implicit assignment cast at first, but that step is not even necessary, date and timestamp share a binary compatible format and can be compared directly. There is a 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
FROM   pg_operator
WHERE  oprname = '<'
AND    oprleft = 'timestamp'::regtype;

Related:

With timestamptz, the matter of time zones would be added to the equation, which is complex but typically burns down to very simple evaluation:

Volatility of now()

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 STABLE, i.e. now() returns the same value within the same transaction.

Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633