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 ?
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
The age() function returns the value as an interval rather than the number of days:
select age(current_date, test_date)
