Number of Years, Months and Days Between Dates

Learn to caculate the difference in years, months and days, between two date field in PostgreSQL.

Suppose you have the following data in a table - indicating the date at which some form of contract commenced:

Let's say you have a table in PostreSQL with a date field. First, we select the dates from the table:

SELECT date_start FROM table_name ORDER BY date_start;

Next, lets substract each date from the current date, limiting the results to the days:

SELECT NOW() - date_start AS days FROM table_name ORDER BY date_start;

Ok. Now we have to filter the results to obtain only the days:

SELECT DATE_PART('days', NOW() - date_start) AS days FROM table_name ORDER BY date_start;

There it is. possible arguments for DATE_PART() are: century, day, decade, dow, doy, epoch, hour, microseconds, millennium, milliseconds, minute, month, quarter, second, timezone, timezone_hour, timezone_minute, week and year. But if you want to get the difference in years, months, and days, do the following:

SELECT AGE(date_start) FROM table_name ORDER BY date_start;