DATEDIFF - Realtime Compute for Apache Flink

本文為您介紹如何使用DATEDIFF函數，計算兩個日期之間的天數差值。

使用限制

僅Realtime Compute引擎VVR 3.0.0及以上版本支援DATEDIFF函數。

文法

INT DATEDIFF(VARCHAR enddate, VARCHAR startdate)
INT DATEDIFF(TIMESTAMP enddate, VARCHAR startdate)
INT DATEDIFF(VARCHAR enddate, TIMESTAMP startdate)
INT DATEDIFF(TIMESTAMP enddate, TIMESTAMP startdate)

入參

參數	資料類型
startdate	TIMESTAMP、VARCHAR
enddate	TIMESTAMP、VARCHAR

說明

VARCHAR日期格式：yyyy-MM-dd或yyyy-MM-dd HH:mm:ss。
如果入參中任何一個為NULL或解析錯誤，則返回為NULL。

樣本

測試資料
datetime1(VARCHAR) datetime2(VARCHAR) nullstr(VARCHAR)
2017-10-15 00:00:00 2017-09-15 00:00:00 NULL

測試語句

SELECT  DATEDIFF(datetime1, datetime2) as int1, 
        DATEDIFF(TIMESTAMP '2017-10-15 23:00:00',datetime2) as int2, 
        DATEDIFF(datetime2,TIMESTAMP '2017-10-15 23:00:00') as int3, 
        DATEDIFF(datetime2,nullstr) as int4, 
        DATEDIFF(nullstr,TIMESTAMP '2017-10-15 23:00:00') as int5, 
        DATEDIFF(nullstr,datetime2) as int6, 
        DATEDIFF(TIMESTAMP '2017-10-15 23:00:00',TIMESTAMP '2017-9-15 00:00:00')as int7
FROM T1;

測試結果
int1(INT) int2(INT) int3(INT) int4(INT) int5(INT) int6(INT) int7(INT)
30 30 -30 NULL NULL NULL 30

datetime1(VARCHAR)	datetime2(VARCHAR)	nullstr(VARCHAR)
2017-10-15 00:00:00	2017-09-15 00:00:00	NULL

int1(INT)	int2(INT)	int3(INT)	int4(INT)	int5(INT)	int6(INT)	int7(INT)
30	30	-30	NULL	NULL	NULL	30