check for null values while finding timestamps from date time-CodePudding

Previously, I was parsing json like this (date and time separately)

SELECT 
    JSON_DATA:"Required_Time"::time as "REQUIRED_TIME",
    JSON_DATA:"Required_Date"::date as "REQUIRED_DATE",
FROM {{ source('INGEST_DATA', 'LOAD' ) }}

However, then I combined both like this:

timestamp_ntz_from_parts(JSON_DATA:"Required_Date"::date,  JSON_DATA:"Required_Time"::time) as "REQUIRED_TIMESTAMP",

The problem is that in some rows, the value of time is NULL even though the date is present. For these cases, the overall returned timestamp value is also NULL. How can I avoid this?

I want to check if the time is NULL, then I want to return either the date only or maybe add 00.00.00 to the date instead of the NULL time value. How can I achieve this?

CodePudding user response：

Here it is:

Case 1: time is null:

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

I get back:

2013-04-05 00:00:00.000

Case 2: time is not null:

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

I get back:

2013-04-05 03:00:00.000