I just needed to get the difference between 2 Timestamps in minutes and couldn't find any useful way on the net, so I decided to add my own solution here, in case someone else needs something similar.
CAST('2010-10-10 10:10:00' AS TIMESTAMP) AS Start_Ts,
CAST('2012-10-10 11:09:00' AS TIMESTAMP) AS End_Ts,
-- Get Days Difference in Minutes (* 24 hours per day * 60 minutes per hour)
(CAST(End_Ts AS DATE) - CAST(Start_Ts AS DATE)) * (24 * 60)
-- Get Hours Difference in Minutes ( * 60 minutes per hour)
(EXTRACT(HOUR FROM End_Ts) - EXTRACT(HOUR FROM Start_Ts)) * 60
-- Get Minutes Difference
(EXTRACT(MINUTE FROM End_Ts) - EXTRACT(MINUTE FROM Start_Ts))
Nothing wrong with your code.
Just for the record - will work with intervall as well and might be more intuitive to read. But will only for if the difference is less than 27.x years.
Thanks for the tip!
The problem is that I have differences of over 50 years in my data and kept getting Overflow errors when working with intervals...
The '(4)' in DAY(4) says the maximum number of digits allowed in a data value. So this will allow up to 9999 days.
Thanks Dave for wuick responce.
I'm trying to get the difference between 2 Timestamps(6) field in minutes. Those start and end time stamp fiels and diffenrence is till now i can see only 10 days. Could you please suggest anything.
It shows a UDF that I wrote which calculates the difference in seconds. A simple change to the code will return minutes. You can either use it as a UDF or just take out the SQL code.