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:
| PostgreSQL Code |
|---|
SELECT date_start FROM table_name ORDER BY date_start; |
| Select all |
Next, lets substract each date from the current date, limiting the results to the days:
| PostgreSQL Code |
|---|
SELECT NOW() - date_start AS days FROM table_name ORDER BY date_start; |
| Select all |
Ok. Now we have to filter the results to obtain only the days:
| PostgreSQL Code |
|---|
SELECT DATE_PART('days', NOW() - date_start) AS days FROM table_name ORDER BY date_start;
|
| Select all |
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:
| PostgreSQL Code |
|---|
SELECT AGE(date_start) FROM table_name ORDER BY date_start; |
| Select all |
Type in the field on the left the code displayed on the image below.
Nobody posted any comments regarding this story. Be the first!
Discuss