In order to get the difference between two dates or datetime(timestamp) in weeks 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 weeks PostgreSQL table.
Difference between two dates in weeks PostgreSQL using simple difference:
Simple Difference symbol is used to compute the difference between two dates in weeks as shown below.
Example 1:
SELECT round(abs('2024-01-14' :: date - '2020-04-01' :: date)/(7),2) as weeks_diff;
output:
Difference between two dates in weeks PostgreSQL using AGE() function:
Within AGE() Function we will be passing two dates on which difference in weeks needs to be calculated, It also divide the results by 7 which will compute difference between two dates in weeks in PostgreSQL.
Example 1:
SELECT round(abs((EXTRACT(epoch from age('2024-01-14', '2020-04-01')) / 86400)::INT)/7,2) as weeks_diff
output:
Difference between two date columns in weeks 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 weeks in postgresql.
Student_detail2:
Simple Difference symbol is used to compute the difference between two date column in the PostgreSQL table in weeks as shown below.
select *,round(abs(birthdaytime :: date - examdatetime :: date)/(7),2) as weeks_diff from student_detail2;
We have typecasted the two datecolumn to date and then found the difference and converted the difference in weeks, so it will be rounded off
Difference between two date columns in weeks 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 weeks 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 weeks in PostgreSQL.
SELECT *, round(ABS((EXTRACT(epoch from age(examdatetime::date, birthdaytime::date))/ 86400)::INT)/7,2) AS weeks_diff from student_detail2;
We have computed the column difference in weeks, so the resultant table will be.
