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