PostgreSQL – How to Get Difference in Days Between Two Timestamps

postgresqltimestamp

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.

Best Answer

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.

Related Question