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');

• Days between 2 Dates: Date can be directly subtrated and return output as integer value in days, can be compared with datediff function

  
  SELECT CAST('2017-07-18' AS DATE) End_Dt, CAST('2009-06-10' AS DATE) Start_Dt, End_Dt-Start_Dt;
  
  Output:
      End_Dt    Start_Dt  (End_Dt-Start_Dt)
  ----------  ----------  -----------------
  2017-07-18  2009-06-10               2960
  

  
  
• Difference in Days to Seconds: Returns output difference values in days, hour, minutes & seconds.

  
  SELECT (DateTimeField1 - DateTimeField2) DAY(4) to SECOND(4) FROM dateTable;
  Output:--> 2 6:56:00  
  Above output is 2 days, 6 hours, 56 minutes & 0 seconds
  

  
  
• Difference in Hours to Seconds: Returns output difference values in hour, minutes & seconds.

  
  SELECT (DateTimeField1 - DateTimeField2) DAY(4) to SECOND(4) FROM dateTable;
  Output:--> 54:56:00
  Above output is 54 hours, 56 minutes & 0 seconds
  

  
  
• Difference in Minutes to Seconds: Returns output difference values in minutes & seconds.

  
  SELECT (DateTimeField1 - DateTimeField2) MINUTE(4) to SECOND(4) FROM dateTable;
  Output:--> 3296:00
  Above output is 3296 minutes  & 0 seconds
  

  
  
• Difference in Seconds: Returns output difference values in seconds.

  
  SELECT (DateTimeField1 - DateTimeField2) SECOND(4) FROM dateTable;
  Output:--> will fail since timestamp difference(197,760) is more than 9999 seconds
  
  
  SELECT (DateTimeField4 - DateTimeField3) SECOND(4) FROM dateTable;
  Output:--> 3750
  Above output is 3750 seconds
  

  
  
  
• Difference in Days

  
  SELECT ((DateTimeField1 - DateTimeField2) DAY(4) to SECOND(4)) AS t1,
         (EXTRACT(DAY from t1) + 
          EXTRACT(HOUR from t1)/24.0 + 
          EXTRACT(MINUTE from t1)/(24*60.0) +
          EXTRACT(SECOND from t1)/(24*60.0*60.0)
          ) AS Required_Output
  FROM dateTable;
  Output: 2.288  
  

  
  
• Difference in Hours

  
  SELECT ((DateTimeField1 - DateTimeField2) DAY(4) to SECOND(4)) AS t1,
         (EXTRACT(DAY from t1)*24 + 
          EXTRACT(HOUR from t1) + 
          EXTRACT(MINUTE from t1)/(60.0) +
          EXTRACT(SECOND from t1)/(60.0*60.0)
          ) AS Required_Output
  FROM dateTable;
  Output: 54.933 
  

  
  
• Difference in Minutes

  
  SELECT ((DateTimeField1 - DateTimeField2) DAY(4) to SECOND(4)) AS t1,
         (EXTRACT(DAY from t1)*(24*60.0) +
          EXTRACT(HOUR from t1)*60.0 + 
          EXTRACT(MINUTE from t1) +
          EXTRACT(SECOND from t1)/60.0
          ) AS Required_Output
  FROM dateTable;
  Output: 3296.00  
  

  
  
• Difference in Seconds

  
  SELECT ((DateTimeField4 - DateTimeField3) DAY(4) to SECOND(4)) AS t1,
         (EXTRACT(DAY from t1)*(24*60*60) +
          EXTRACT(HOUR from t1)*(60*60) + 
          EXTRACT(MINUTE from t1)*60 +
          EXTRACT(SECOND from t1)
          ) AS Required_Output
  FROM dateTable;
  Output: 3750
  

  
  

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)

If you want timestamp difference only in hours (ignoring minutes & Seconds)

SELECT  ((DateTimeField1 - DateTimeField2) HOUR(4))+( (DateTimeField4 - DateTimeField4) HOUR(4)) required_output
FROM dateTable;


And if you want timestamp difference considering hours,minutes & Seconds


SELECT
   (DateTimeField1 - DateTimeField2) DAY(4) to SECOND(4) t1,
   (DateTimeField4 - DateTimeField3)  HOUR(4) to SECOND(4) t2,
   EXTRACT(HOUR from t1)*24 + EXTRACT(HOUR from t1) + EXTRACT(MINUTE from t1)/60.0 + EXTRACT(SECOND from t1)/(60.0*60.0) t1_HOUR,
   EXTRACT(HOUR from t2)+EXTRACT(MINUTE from t2)/60.0 +EXTRACT(SECOND from t2)/(60.0*60.0) t2_HOUR,
   t1_HOUR+t2_HOUR required_output
FROM dateTable;

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