CODEWITHSUNDEEP
sqlteradata
Teja Goud Kandula
Teja Goud Kandula

Reputation: 1574

Teradata timestamp subtraction with difference in minutes

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?

Upvotes: 2

Views: 10387

Answers (1)

eshirvana
eshirvana

Reputation: 24568

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

Upvotes: 1

Related Questions