5

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.

  • And by whose status has been updated since more than 10 days you mean whose status has not been updated since more than 10 days, right? – Erwin Brandstetter Jul 2 '15 at 21:26
  • Exactly, I meant not, will edit the question. – flyer88 Jul 2 '15 at 21:33
11

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):

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

You can just subtract integer from 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.

For 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 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 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.

| improve this answer | |
  • Thanks a lot @erwin-brandstetter for the complete answer and extra info. Learnt a bit about STABLE functions and sargables. The solution is clear, however I still have a doubt. In your second solution [...] status_updated_at < CURRENT_DATE - 10 I don't understand how the comparisson and coercion works. status_updated_at is a timestamp. CURRENT_DATE is a date. First of all, when you are substracting 10 from CURRENT_DATE why that 10 represents days and not other time unit. Finally, in the < comparisson, I am comparing a timestamp vs a date, date is coerced to a timestamp? – flyer88 Jul 3 '15 at 16:17
  • 1
    @flyer88: I added a detailed explanation. – Erwin Brandstetter Jul 3 '15 at 17:00
  • Thanks for link to wiktionary.org, @Colin! Wikipedia used to have an article for "sargable" until recently. Obviously, it was deleted. – Erwin Brandstetter Dec 15 '17 at 13:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.