Postgres: Get number of days since date

user_1316501 0
Viewed 9021

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!

postgresql
3 Answers

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
user_132116750
Related