CODEWITHSUNDEEP
sqltimetimestampteradata
Himanshu Pant
Himanshu Pant

Reputation: 37

Calculate Difference between time in Teradata

I want to take difference of 2 date fields but both are varchar field

SyntaxEditor Code Snippet :

start_time- 2018-03-02 06:31:22

end_time -  2018-03-02 06:33:32.478000

I want the result in integer as 2 in min always and it should be an integer always

Result :- 2

It would be very great if anyone can help to achieve my case.

Thanks

Upvotes: 0

Views: 6399

Answers (1)

dnoeth
dnoeth

Reputation: 60462

You can utilize interval calculations like cast((cast(end_time as timestamp) - cast(start_time as timestamp) minute(4)) as int), but it will fail for > 9999 minutes.

This is SQL UDF for calculating the difference of timestamps in seconds without limitations:

REPLACE FUNCTION TimeStamp_Diff_Seconds
(
   ts1 TIMESTAMP(6)
  ,ts2 TIMESTAMP(6)
)
RETURNS DECIMAL(18,6)
LANGUAGE SQL
CONTAINS SQL
RETURNS NULL ON NULL INPUT
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
(CAST((CAST(ts2 AS DATE)- CAST(ts1 AS DATE)) AS DECIMAL(18,6)) * 60*60*24)
      + ((EXTRACT(  HOUR FROM ts2) - EXTRACT(  HOUR FROM ts1)) * 60*60)
      + ((EXTRACT(MINUTE FROM ts2) - EXTRACT(MINUTE FROM ts1)) * 60)
      +  (EXTRACT(SECOND FROM ts2) - EXTRACT(SECOND FROM ts1))
;

If you can't create UDFs you can copy the source and apply a final / 60 and cast it as integer.

Upvotes: 1

Related Questions