In order to get the difference between two dates or datetime(timestamp) in days 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 extract() function. 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 days PostgreSQL table.
Difference between two dates in days PostgreSQL using simple difference:
Simple Difference symbol is used to compute the difference between two dates in days as shown below.
Example 1:
SELECT '2024-02-28'::date - '2024-02-24'::date AS date_difference_in_days
output:
Difference between two dates in days PostgreSQL using EXTRACT() :
Within EXTRACT() Function we will be passing two dates on which difference in dates needs to be calculated, It also takes DAY as the argument which will compute difference between two dates in days in PostgreSQL.
Example 1:
SELECT EXTRACT(DAY FROM '2024-02-28 15:30:00'::timestamp - '2024-02-24 10:45:00'::timestamp) AS days_difference_in_days;
output:
Difference between two date columns in days 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 days in postgresql.
Student_detail2:
Simple Difference symbol is used to compute the difference between two date column in the PostgreSQL table in days as shown below.
SELECT *, examdatetime::date - birthdaytime::date As date_diff_in_days from Student_detail2
We have typecasted the two datecolumn to date and then found the difference, so it will be rounded off
Method 2
To find the difference between two date columns in days and hours we have used below method
SELECT *, examdatetime::timestamp - birthdaytime::timestamp As date_diff_in_days from Student_detail2
We have typecasted the two datecolumn to timestamp and then found the difference, so the resultant column to find the date difference in days will have count of days followed by difference in hours as shown below
Difference between two date columns in days 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 days in postgresql.
Student_detail2:
Within EXTRACT() Function we will be passing two dates column on which difference in dates needs to be calculated, It also takes DAY as the argument which will compute difference between two date column in days in PostgreSQL.
SELECT *,EXTRACT(DAY FROM examdatetime::timestamp - birthdaytime::timestamp) AS date_diff_in_days from Student_detail2;
We have typecasted the two date column to timestamp and then found the difference, so the resultant table will be.
