Teradata时间戳做差返回秒，

和通数据库htsjk.Com2019-07-19 22:03 来源:未知阅读:10635 评论 482 热度3

标签：

Teradata时间戳做差返回秒，

--创建函数
REPLACE FUNCTION timestampdiff_char19(endtime VARCHAR(19),starttime VARCHAR(19))
RETURNS INT
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN 
    
 ( CAST(CAST(endtime as TIMESTAMP(0)) AS DATE FORMAT 'YYYYMMDD')-CAST(CAST(starttime as TIMESTAMP(0)) AS DATE FORMAT 'YYYYMMDD'))*24*3600
   +EXTRACT(HOUR FROM (TO_TIMESTAMP(endtime,'YYYY-MM-DD HH24:MI:SS' )-TO_TIMESTAMP(SUBSTR(endtime,1,10)||' '||SUBSTR(starttime,12,8),'YYYY-MM-DD HH24:MI:SS' ) HOUR TO SECOND))*3600
   +EXTRACT(MINUTE FROM (TO_TIMESTAMP(endtime,'YYYY-MM-DD HH24:MI:SS' )-TO_TIMESTAMP(SUBSTR(endtime,1,10)||' '||SUBSTR(starttime,12,8),'YYYY-MM-DD HH24:MI:SS' ) HOUR TO SECOND))*60
   +EXTRACT(SECOND FROM (TO_TIMESTAMP(endtime,'YYYY-MM-DD HH24:MI:SS' )-TO_TIMESTAMP(SUBSTR(endtime,1,10)||' '||SUBSTR(starttime,12,8),'YYYY-MM-DD HH24:MI:SS' ) HOUR TO SECOND))
;

传入的是字符返回秒