In order to get the difference between two dates or datetime(timestamp) in hours in PostgreSQL, We will be using simple difference symbol (subtraction). In a roundabout way to find difference between two dates in hours 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 hours PostgreSQL table.
Difference between two datetime (timestamp) in hours PostgreSQL using simple difference:
Simple Difference symbol is used to compute the difference between two dates in hours as shown below.
Example 1:
SELECT round(abs('2024-04-01 09:12:00' :: date - '2020-04-14 09:12:00' :: date)*(24),2) as hours_diff;
Output:
Difference between two datetime (timestamp) columns in hours 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 hours in postgresql.
Student_detail2:
Simple Difference symbol is used to compute the difference between two date column in the PostgreSQL table in hours as shown below.
select *,round(abs(birthdaytime :: date - examdatetime :: date)*(24),2) as hours_diff from student_detail2;
We have typecasted the two date columns to date and then found the difference and converted the difference in hours, so it will be rounded off
Difference between two datetime (timestamp) in hours 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 86400. In addition to that we will using date_part() function which will compute difference between two dates in hours in PostgreSQL.
Example 1:
SELECT COALESCE(round(abs((EXTRACT(epoch from age('2024-04-01 09:12:00', '2020-04-14 09:12:00')) / 86400)::int),2)::int + DATE_PART('month', age('2024-04-01 09:12:00', '2020-04-14 09:12:00'))::float*0.454545::float)*24 as hours_diff
output:
Difference between two datetime (timestamp) columns in hours 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 hours 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 and also date_part() which will compute difference between two date column in hours in PostgreSQL.
SELECT *,COALESCE(round(abs((EXTRACT(epoch from age(examdatetime::date, birthdaytime::date)) / 86400)::INT),2)::int + DATE_PART('month', age(examdatetime::date, birthdaytime::date))::float*0.454545::float)*24 as hours_diff  from student_detail2;
We have computed the column difference in hours, so the resultant table will be.
