Teradata timestamp subtraction with difference in minutes

user_9087250 0
Viewed 1990
select StartDatetime,EndDatetime,
(TRIM((COALESCE(CAST((CAST(EndDatetime AS TIME) - CAST(StartDatetime AS TIME) MINUTE(4)) AS INT),'')))) as TimeDiff
From Table1

EndDatetime and StartDatetime are columns of type Timestamp(6). The above query gave the difference in between the timestamps in minutes. It is working if both the timestamps are present in the same day. But when EndDatetime is having a value of tomorrow wrt to the StartDatetime then the TimeDiff value that is being returned is a negative value. Using absolute function is not a best solution because then the TimeDiff number itself isn't accurate.

How to get the timestamp difference precisely in minutes?

teradata sql
1 Answers

here is one way by using extract :

Select 
    StartDatetime,EndDatetime
    ,(CAST((CAST(EndDatetime AS DATE)- CAST(StartDatetime AS DATE)) AS DECIMAL(18,6)) * 60*24)
    + ((EXTRACT(HOUR FROM EndDatetime) - EXTRACT(HOUR FROM StartDatetime))* 60)
    + ((EXTRACT(MINUTE FROM EndDatetime) - EXTRACT(MINUTE FROM StartDatetime)))
    + ((EXTRACT(SECOND FROM EndDatetime) - EXTRACT(SECOND FROM StartDatetime))/60)
AS "Difference in Minutes"
from Table1
user_13674540
Related