In order to get the difference between two dates or datetime(timestamp) in months in PostgreSQL, We will be using simple difference symbol (subtraction). In a roundabout way to find difference between two dates 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 months PostgreSQL table.
Difference between two dates in months PostgreSQL using simple difference:
Simple Difference symbol is used to compute the difference between two dates in months as shown below.
Example 1:
SELECT round(abs('2024-01-14' :: date - '2020-04-16' :: date)/(365.25/12),2) as month_diff;
Difference between two dates in months 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 30.4 which will compute difference between two dates in months in PostgreSQL.
Example 1:
SELECT round(abs((EXTRACT(epoch from age('2024-01-14', '2020-04-16')) / 86400)::INT)/30.35,2) as month_diff
Difference between two date columns in months 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 months in postgresql.
Student_detail2:
Simple Difference symbol is used to compute the difference between two date column in the PostgreSQL table in months as shown below.
select *,round(abs(birthdaytime :: date - examdatetime :: date)/(365.25/12),2) as diff_months from student_detail2;
We have typecasted the two datecolumn to date and then found the difference and converted the difference in months, so it will be rounded off
Difference between two date columns in months 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 months 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 months in PostgreSQL.
SELECT *, round(ABS((EXTRACT(epoch from age(examdatetime::date, birthdaytime::date))/ 86400)::INT)/30.35,2) AS diff_months from student_detail2;
We have computed the column difference in months, so the resultant table will be.
