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!
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;
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)
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
;
