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.

Strange cheque-reading code in form
Count All Rows in a Schema

Comments

  1. Compact but great post!

    I was struggling on method#2 when working with durations with fractional seconds………

    Thnx for helping me out.

  2. This helped a lot!

  3. Thanks a lot, i found many solutions, only yours works 🙂

Leave a Reply

Your email address will not be published / Required fields are marked *