Postgres: Get number of days since date

Question

I have a table with information about articles. I have a published_at column that's a timestamp without time zone and want to calculate how long ago each article was published, in days.

So if it was published a week ago, I should get back 7.

Any idea how to go about?

I know Postgres has NOW() but can I just subtract and then take DAY()?

Thanks!

Answer 1

You can subtract one date from another, the result will be the number of days between the two dates. If one of them is a timestamp, just cast it to a date:

select current_date - published_at::date
from your_table;

If you want to get those that are younger then 7 days, you can use:

select current_date - published_at::date as age_in_days
from your_table
where published_at >= current_date - 7;

Answer 2

you can use date_trunc, eg:

t=# select date_trunc('day',now() - '2016-01-01'::date);
 date_trunc
------------
 580 days
(1 row)

or for digit result, extract

t=# select extract('day' from date_trunc('day',now() - '2016-01-01'::date));
 date_part
-----------
       580
(1 row)

Answer 3

You should also be able to use these slightly different versions.

SELECT
    NOW() - published_at, -- returns X days hh:mm:ss...
    EXTRACT(DAY FROM NOW() - published_at), -- returns difference in days
    CURRENT_DATE - published_at -- returns difference in days
;

• more info here for the potential effects of doing operations with/without timestamps
• an old thread reporting the potential performance impacts of NOW() vs CURRENT_DATE