--start of the day, no tz
select to_timestamp_ntz('2019-05-18');
--2019-05-18 00:00:00.000000000

--!! uses system time
select to_timestamp_tz('2019-05-18');
--2019-05-18 00:00:00.000000000 -07:00

--!! does nothing
select CONVERT_TIMEZONE('UTC','UTC', to_timestamp_ntz('2019-05-18'));
--2019-05-18 00:00:00.000000000

select CONVERT_TIMEZONE('UTC', to_timestamp_tz('2019-05-18'));

--!! assumes system time, and casts to timestamp_tz
select CONVERT_TIMEZONE('UTC', CONVERT_TIMEZONE('UTC','UTC', to_timestamp_ntz('2019-05-18')));
--2019-05-18 07:00:00.000000000 +00:00

-- works as expected, but still has no timezone
select CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', to_timestamp_ntz('2019-05-18'));

-- seems to add the timezone
select CONVERT_TIMEZONE('UTC', to_timestamp_ntz('2019-05-18') || ' +0:00');
-- 2019-05-18 00:00:00.000000000 +00:00

-- works
select CONVERT_TIMEZONE('America/Los_Angeles', to_timestamp_ntz('2019-05-18') || ' +0:00');
-- 2019-05-17 17:00:00.000000000 -07:00