Timestamp Difference in Minutes (without using Periods)

Database
Teradata Employee

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

2 REPLIES
Supporter

Re: Timestamp Difference in Minutes (without using Periods)

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,  

;"]

Teradata Employee

Re: Timestamp Difference in Minutes (without using Periods)

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