问题描述
之前,我是这样解析json的(日期和时间分开)
SELECT
JSON_DATA:"Required_Time"::time as "REQUIRED_TIME",
JSON_DATA:"Required_Date"::date as "REQUIRED_DATE",
FROM {{ source('INGEST_DATA', 'LOAD' ) }}
然而,然后我将两者组合如下:
timestamp_ntz_from_parts(JSON_DATA:"Required_Date"::date, JSON_DATA:"Required_Time"::time) as "REQUIRED_TIMESTAMP",
问题是,在某些行中,即使存在日期,时间值也是空的。对于这些情况,总体返回的时间戳值也为空。我如何才能避免这种情况?
我想检查时间是否为空,然后我想只返回日期,或者可能将00.00.00添加到日期而不是空时间值。我如何实现这一点?
推荐答案
如下:
案例1:时间为空:
set mydate = '2013-04-05';
set mytime = NULL;
SELECT timestamp_ntz_from_parts($MYDATE::date, IFF($MYTIME::time IS NULL, '00:00:00'::time, $MYTIME));
我回来了:
2013-04-05 00:00:00.000
案例2:时间不为空:
set mydate = '2013-04-05';
set mytime = '03:00:00';
SELECT timestamp_ntz_from_parts($MYDATE::date, IFF($MYTIME::time IS NULL, '00:00:00'::time, $MYTIME));
我回来了:
2013-04-05 03:00:00.000