Datediff between two dates

Community Building Data & reporting

AI-generated summary

SStrong wants to calculate the difference in days between a topic’s creation date and the current date using PostgreSQL. JammyDodger suggests simplifying the calculation using CURRENT_DATE - t.created_at::date. This can be used to determine the number of days since a topic was created. JammyDodger also mentions the reltime function for relative time, but notes it’s more useful for intervals under 30 days. SStrong expresses gratitude for the helpful solution.

1

I’ve tried following the below link to work this out myself but cannot quickly enough

What I would simply like to do is a date diff between topic created date and today’s date to be able to say “this topic is 6 days old”

These are the two fields I am working with:

to_char(date_trunc(‘DAY’, t.created_at)::date,‘DD/MM/YYYY’) AS “Topic created date”,
to_char(date_trunc(‘DAY’, now())::date,‘DD/MM/YYYY’) AS “Today date”

PostgreSQL: Documentation: 9.6: Date/Time Functions and Operators

Thanks

2

I think you could simplify that to something like:

SELECT 
    t.id AS topic_id,
    CURRENT_DATE - t.created_at::date AS days_old
FROM topics t
ORDER BY t.created_at DESC

There’s also a nifty reltime$time you can use to give relative time (though it’s more useful for intervals of less than 30 days or it gives dates instead)

SELECT 
    t.id AS topic_id,
    t.created_at AS reltime$time
FROM topics t
ORDER BY t.created_at DESC
3

Excellent - thanks so much for this