Difference Between Two Timestamps
Easy, right? Perhaps not as straightforward as you’d think.
Method #1: use EXTRACT
extract(day from (x-y))*24*60*60 + extract(hour from (x-y))*60*60 + extract(minute from (x-y))*60 + extract(second from (x-y))
Method #2: use CAST
( CAST( x AS DATE ) - CAST( y AS DATE ) ) * 86400
The difference? Method #2 is faster (my tests indicate faster by a factor of 3), but does not include fractional seconds. Method #1 is a bit slower, but includes fractions of a second. See the SO link for details.
Hans.Wijnacker
2 January 2020 - 10:04 pm
Compact but great post!
I was struggling on method#2 when working with durations with fractional seconds………
Thnx for helping me out.
Vinoy
21 August 2020 - 3:46 am
This helped a lot!
Max
17 March 2022 - 2:22 pm
Thanks a lot, i found many solutions, only yours works 🙂