All Forums Database
Fernando_Araujo 2 posts Joined 04/10
12 Mar 2012
Timestamp Difference in Minutes (without using Periods)

Hi,

 

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.

 

SEL
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))
AS Total_Minutes
;

 

Regs,

 

Fernando

f.A:
ulrich 816 posts Joined 09/09
12 Mar 2012

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.

 

SEL

CAST('2010-10-10 10:10:00'  AS TIMESTAMP) AS Start_Ts,

CAST('2012-10-10 11:09:00'  AS TIMESTAMP) AS End_Ts,

 

(end_ts - start_ts) day(4) to minute as diff_DDHHMM,

 

Extract(day from diff_DDHHMM) * 24*60

+ Extract(hour from diff_DDHHMM) * 60

+ Extract(minute from diff_DDHHMM) as Total_Minutes,  

;"]

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Fernando_Araujo 2 posts Joined 04/10
12 Mar 2012

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...

Regs,

 

Fernando

f.A:

You must sign in to leave a comment.