Teradata Timestamp difference is frequently required operation to determine duration between two events occurred at Time T1 & Time T2.

Teradata: Subtract Timestamps

Timestamp difference is frequently required operation to determine duration between two events occurred at Time T1 & Time T2. The required duration can be in days, hours, minutes or even in seconds.


Lets create a volatile table & populated data for illustration purpose.
CREATE volatile table dateTable
(    DateTimeField1  TIMESTAMP(0),
     DateTimeField2  TIMESTAMP(0),
     DateTimeField3 TIMESTAMP(0),
     DateTimeField4 TIMESTAMP(0)
)
ON COMMIT PRESERVE ROWS;

INSERT INTO dateTable VALUES ('2018-08-05 11:11:23','2018-08-03 04:15:23','2018-08-04 12:11:23','2018-08-04 13:13:53');



Use case where difference of multiple timestamp is happening

Output is required in hours from combination of 4 timestamps. Here requirement to sum combination of 2 timestamp (DateTimeField1 + DateTimeField3) & (DateTimeField2 + DateTimeField4) and then subtract it from other.

In simple words, requirement is to find hours difference from
(DateTimeField1 + DateTimeField4) - (DateTimeField2 + DateTimeField3)
Above statement is equivalent to
(DateTimeField1 - DateTimeField2) + (DateTimeField4 - DateTimeField3)



Note: Above logic can handle time difference for maximum of 9999 value for each day, hour, minute & second between 2 timestamp, if any of the value exceed 9999 then query will fail.

Visit Teradata Date Functions if you want to learn more about date functions & Interval functions