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

7 REPLIES
Senior 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

New Member

Re: Timestamp Difference in Minutes (without using Periods)

Thanks ulrich. It worked :) 

Re: Timestamp Difference in Minutes (without using Periods)

day(4) to minute --- What is meaning for this function. Especially day (4) meaning

Senior Apprentice

Re: Timestamp Difference in Minutes (without using Periods)

The '(4)' in DAY(4) says the maximum number of digits allowed in a data value. So this will allow up to 9999 days.

 

HTH
Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com

Re: Timestamp Difference in Minutes (without using Periods)

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.

 

Thanks

Prasad

Senior Apprentice

Re: Timestamp Difference in Minutes (without using Periods)

Hi Prasad,

 

Have a look at this https://community.teradata.com/t5/Data-Modeling/Calculating-hours-minutes-and-seconds-from-two-times...

 

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.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com