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;