计算Teradata中的时间差

您可以利用间隔计算,如 cast((cast(end_time as timestamp) - cast(start_time as timestamp) minute(4)) as int) ,但它将失败,因为>9999分钟。

这是SQL UDF,用于以秒为单位计算时间戳的差异,没有任何限制:

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

如果无法创建自定义项,可以复制源并应用最终 / 60 并将其转换为整数。