In order to get the difference between two dates or datetime(timestamp) in years in PostgreSQL, We will be using simple difference symbol (subtraction). In a roundabout way to find difference between two dates in years in PostgreSQL we can also use age() function with epoch. Let’s see an example of all the two approaches. We will also consider an example on how to get difference between two dates or datetime(timestamp) column in years PostgreSQL table.
Difference between two dates in years PostgreSQL using simple difference:
Simple Difference symbol is used to compute the difference between two dates in years as shown below.
Example 1:
SELECT round(abs((EXTRACT(epoch from age('2024-01-14', '2020-04-01')) / 86400)::INT)/365.25,2) as year_diff
output:
Difference between two dates in years PostgreSQL using AGE() function:
Within AGE() Function we will be passing two dates on which difference in dates needs to be calculated, It also divide the results by 365.25 which will compute difference between two dates in years in PostgreSQL.
Example 1:
SELECT round(abs('2024-01-14' :: date - '2020-04-01' :: date)/(365.25),2) as year_diff;
output:
Difference between two date columns in years PostgreSQL :
Method 1
We will be using below Student_detail2 table for our example to depict on how to find difference between two date columns in years in postgresql.
Student_detail2:
Simple Difference symbol is used to compute the difference between two date column in the PostgreSQL table in years as shown below.
select *,round(abs(birthdaytime :: date - examdatetime :: date)/(365.25),2) as year_diff from student_detail2;
We have typecasted the two date column to date and then found the difference and converted the difference in years, so it will be rounded off
Difference between two date columns in years PostgreSQL using EXTRACT() :
We will be using below Student_detail2 table for our example to depict on how to find difference between two date columns in years in postgresql.
Student_detail2:
Within EXTRACT() Function we will be passing two dates column on which difference in dates needs to be calculated, It uses EPOCH function along with Age() function which will compute difference between two date column in years in PostgreSQL.
SELECT *, round(ABS((EXTRACT(epoch from age(examdatetime::date, birthdaytime::date))/ 86400)::INT)/365.25,2) AS year_diff from student_detail2;
We have computed the column difference in years, so the resultant table will be.
