CODEWITHSUNDEEP
sqlpostgresql
ahmedshahriar
ahmedshahriar

Reputation: 1076

How to Find the Interval Between Two Dates in PostgreSQL

I have a 'test_date' column in 'test' table in Postgres DB, ex - (2018-05-29)

I need to calculate difference between current date and that date column and return the result as days, months, years.

I tried -

select (current_date - test_date) from test;

but it returns the values as days. but I need the result as days, months, years. How to convert it properly ?

Upvotes: 1

Views: 3268

Answers (2)

user330315
user330315

Reputation:

If you use a timestamp then you'll get a `interval' back:

select justify_interval(date_trunc('day', current_timestamp) - test_date)

The date_trunc() is there to set the time part of the timestamp to 00:00:00. By default that would return an interval with only days in it. The justify_interval() will then "normalize" this to months, weeks and days.

E.g. 0 years 7 mons 28 days 0 hours 0 mins 0.0 secs

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

The age() function returns the value as an interval rather than the number of days:

select age(current_date, test_date)

Upvotes: 2

Related Questions