Databricks Notebook (SQL) - Set Time Zone

Question

Trying to deal with an issue in databricks where the logging framework we have put in place is logging the wrong time (using GETDATE() or CURRENT_TIMESTAMP()). We attempted to resolve this by using 'SET TIME ZONE' but it appears this is being ignored in this SQL statement?

This is from one cell in a databricks notebook, despite setting the value, the inserted time is wrong.

SET TIME ZONE 'Europe/London';

INSERT INTO ${catalogName}.logging.RowCountHistory 
  (WorkHistoryID, TaskName, EventDateTime, RowCount)
SELECT 
  (SELECT WorkHistoryID FROM ${catalogName}.logging.workhistory WHERE LookUpID = '${generatedId}' LIMIT 1)
   AS WorkHistoryID,
  '${taskName}' AS TaskName,
  current_timestamp() AS EventDateTime,
  CASE WHEN LENGTH('${rowCount}') > 0 THEN bigint(${rowCount}) ELSE NULL END;

However when I do the following, it shows the correct time -

SET TIME ZONE 'Europe/London';
SELECT current_timestamp();

Open to an alternate approach if needed, did read about CONVERT_TIMEZONE, but couldnt make sense of the documentation.

Answer 1

create table tztest (id int, ts TimeStamp);

insert into tztest values (1,current_timestamp());
select * from tztest;

insert into tztest values (2,convert_timezone('America/New_York','Europe/London',current_timestamp()));
select * from tztest;