Get difference between two timestamps in postgresql by hours, minutes, Seconds and milliseconds

In Order to get difference between two timestamps in postgresql by hours, minutes, seconds and milliseconds we will be using EPOCH time difference. Let’s see how to

  • Get difference between two timestamp in postgresql by hours with an example.
  • Get difference between two timestamp in postgresql by minutes with an example.
  • Get difference between two timestamp in postgresql by seconds with an example.
  • Get difference between two timestamp in postgresql by milliseconds with an example.

Table we use is student_detail2

Get difference between two timestamps in postgresql by hours, minutes, Seconds and milliseconds 1

 

Get difference between two timestamp in postgresql by hours with an example:

Difference between two timestamp in hours can be calculated using EPOCH function by dividing 3600 because EPOCH returns difference in seconds as shown below


select *,EXTRACT(EPOCH FROM (birthtime -fin_time ))/3600  as diff_time_hours from student_detail2;

So the resultant table will be

Get difference between two timestamps in postgresql by hours, minutes, Seconds and milliseconds 2

 

Get difference between two timestamp in postgresql by minutes with an example:

Difference between two timestamp in minutes can be calculated using EPOCH function by dividing 60 because EPOCH returns difference in seconds as shown below


select *,EXTRACT(EPOCH FROM (birthtime -fin_time ))/60  as diff_time_min from student_detail2;

So the resultant table will be

Get difference between two timestamps in postgresql by hours, minutes, Seconds and milliseconds 3

 

Get difference between two timestamp in postgresql by seconds with an example:

Difference between two timestamp in seconds can be calculated using EPOCH function. EPOCH returns difference in seconds as shown below


select *,EXTRACT(EPOCH FROM (birthtime -fin_time ))  as diff_time_sec from student_detail2;

So the resultant table will be

Get difference between two timestamps in postgresql by hours, minutes, Seconds and milliseconds 4

 

Get difference between two timestamp in postgresql by milliseconds with an example:

Difference between two timestamp in milliseconds can be calculated using EPOCH function by dividing 1000 because EPOCH returns difference in seconds as shown below


select *,EXTRACT(EPOCH FROM (birthtime -fin_time ))*1000  as diff_time_millisec from student_detail2;

So the resultant table will be

Get difference between two timestamps in postgresql by hours, minutes, Seconds and milliseconds 5