Summary/Reported Issue
Using the convert_timezone function with a column in a query causes the query to fail with IllegalArgumentException: The argument ''destTz' of function 'convert_timezone' has to be constant!
Relevant Versions
Dremio 25.1.x , 26.0.x
Troubleshooting Steps
There is a defect with internal reference DX-100635 that describes the issue and at this time is being investigated by engineering
Steps to Resolve
This issue can be addressed by creating a UDF that uses a case statement for dynamic timezone conversion. Please refer to the example below
CREATE OR REPLACE FUNCTION ctz(timezone varchar , ts TIMESTAMP) RETURNS TIMESTAMP
RETURN SELECT
CASE
WHEN timezone = 'Africa/Cairo' THEN CONVERT_TIMEZONE('Africa/Cairo', ts)
WHEN timezone = 'Africa/Johannesburg' THEN CONVERT_TIMEZONE('Africa/Johannesburg', ts)
WHEN timezone = 'America/Argentina/Buenos_Aires' THEN CONVERT_TIMEZONE('America/Argentina/Buenos_Aires', ts)
WHEN timezone = 'America/Argentina/Catamarca' THEN CONVERT_TIMEZONE('America/Argentina/Catamarca', ts)
WHEN timezone = 'America/Blanc-Sablon' THEN CONVERT_TIMEZONE('America/Blanc-Sablon', ts)
WHEN timezone = 'America/Bogota' THEN CONVERT_TIMEZONE('America/Bogota', ts)
WHEN timezone = 'America/Chicago' THEN CONVERT_TIMEZONE('America/Chicago', ts)
WHEN timezone = 'America/Creston' THEN CONVERT_TIMEZONE('America/Creston', ts)
WHEN timezone = 'America/Detroit' THEN CONVERT_TIMEZONE('America/Detroit', ts)
WHEN timezone = 'America/Edmonton' THEN CONVERT_TIMEZONE('America/Edmonton', ts)
WHEN timezone = 'America/Glace_Bay' THEN CONVERT_TIMEZONE('America/Glace_Bay', ts)
WHEN timezone = 'America/Goose_Bay' THEN CONVERT_TIMEZONE('America/Goose_Bay', ts)
WHEN timezone = 'America/Guatemala' THEN CONVERT_TIMEZONE('America/Guatemala', ts)
WHEN timezone = 'America/Indiana/Indianapolis' THEN CONVERT_TIMEZONE('America/Indiana/Indianapolis', ts)
WHEN timezone = 'America/Indiana/Marengo' THEN CONVERT_TIMEZONE('America/Indiana/Marengo', ts)
WHEN timezone = 'America/Indiana/Petersburg' THEN CONVERT_TIMEZONE('America/Indiana/Petersburg', ts)
WHEN timezone = 'America/Indiana/Winamac' THEN CONVERT_TIMEZONE('America/Indiana/Winamac', ts)
WHEN timezone = 'America/Kentucky/Louisville' THEN CONVERT_TIMEZONE('America/Kentucky/Louisville', ts)
WHEN timezone = 'America/Kentucky/Monticello' THEN CONVERT_TIMEZONE('America/Kentucky/Monticello', ts)
WHEN timezone = 'America/Kralendijk' THEN CONVERT_TIMEZONE('America/Kralendijk', ts)
WHEN timezone = 'America/Lima' THEN CONVERT_TIMEZONE('America/Lima', ts)
WHEN timezone = 'America/Los_Angeles' THEN CONVERT_TIMEZONE('America/Los_Angeles', ts)
WHEN timezone = 'America/Mexico_City' THEN CONVERT_TIMEZONE('America/Mexico_City', ts)
WHEN timezone = 'America/Montevideo' THEN CONVERT_TIMEZONE('America/Montevideo', ts)
WHEN timezone = 'America/New_York' THEN CONVERT_TIMEZONE('America/New_York', ts)
WHEN timezone = 'America/Panama' THEN CONVERT_TIMEZONE('America/Panama', ts)
WHEN timezone = 'America/Rio_Branco' THEN CONVERT_TIMEZONE('America/Rio_Branco', ts)
WHEN timezone = 'America/Santiago' THEN CONVERT_TIMEZONE('America/Santiago', ts)
WHEN timezone = 'America/Sao_Paulo' THEN CONVERT_TIMEZONE('America/Sao_Paulo', ts)
WHEN timezone = 'America/St_Johns' THEN CONVERT_TIMEZONE('America/St_Johns', ts)
WHEN timezone = 'America/Tegucigalpa' THEN CONVERT_TIMEZONE('America/Tegucigalpa', ts)
WHEN timezone = 'America/Toronto' THEN CONVERT_TIMEZONE('America/Toronto', ts)
WHEN timezone = 'America/Vancouver' THEN CONVERT_TIMEZONE('America/Vancouver', ts)
WHEN timezone = 'America/Winnipeg' THEN CONVERT_TIMEZONE('America/Winnipeg', ts)
WHEN timezone = 'Asia/Bangkok' THEN CONVERT_TIMEZONE('Asia/Bangkok', ts)
WHEN timezone = 'Asia/Ho_Chi_Minh' THEN CONVERT_TIMEZONE('Asia/Ho_Chi_Minh', ts)
WHEN timezone = 'Asia/Jakarta' THEN CONVERT_TIMEZONE('Asia/Jakarta', ts)
WHEN timezone = 'Asia/Karachi' THEN CONVERT_TIMEZONE('Asia/Karachi', ts)
WHEN timezone = 'Asia/Kolkata' THEN CONVERT_TIMEZONE('Asia/Kolkata', ts)
WHEN timezone = 'Asia/Kuala_Lumpur' THEN CONVERT_TIMEZONE('Asia/Kuala_Lumpur', ts)
WHEN timezone = 'Asia/Kuching' THEN CONVERT_TIMEZONE('Asia/Kuching', ts)
WHEN timezone = 'Asia/Manila' THEN CONVERT_TIMEZONE('Asia/Manila', ts)
WHEN timezone = 'Asia/Qatar' THEN CONVERT_TIMEZONE('Asia/Qatar', ts)
WHEN timezone = 'Asia/Seoul' THEN CONVERT_TIMEZONE('Asia/Seoul', ts)
WHEN timezone = 'Asia/Singapore' THEN CONVERT_TIMEZONE('Asia/Singapore', ts)
WHEN timezone = 'Asia/Taipei' THEN CONVERT_TIMEZONE('Asia/Taipei', ts)
WHEN timezone = 'Asia/Tokyo' THEN CONVERT_TIMEZONE('Asia/Tokyo', ts)
WHEN timezone = 'Australia/Melbourne' THEN CONVERT_TIMEZONE('Australia/Melbourne', ts)
WHEN timezone = 'Australia/North' THEN CONVERT_TIMEZONE('Australia/North', ts)
WHEN timezone = 'Australia/Perth' THEN CONVERT_TIMEZONE('Australia/Perth', ts)
WHEN timezone = 'Australia/Queensland' THEN CONVERT_TIMEZONE('Australia/Queensland', ts)
WHEN timezone = 'Australia/Sydney' THEN CONVERT_TIMEZONE('Australia/Sydney', ts)
WHEN timezone = 'Australia/West' THEN CONVERT_TIMEZONE('Australia/West', ts)
WHEN timezone = 'Brazil/Acre' THEN CONVERT_TIMEZONE('Brazil/Acre', ts)
WHEN timezone = 'Europe/Amsterdam' THEN CONVERT_TIMEZONE('Europe/Amsterdam', ts)
WHEN timezone = 'Europe/Andorra' THEN CONVERT_TIMEZONE('Europe/Andorra', ts)
WHEN timezone = 'Europe/Athens' THEN CONVERT_TIMEZONE('Europe/Athens', ts)
WHEN timezone = 'Europe/Belgrade' THEN CONVERT_TIMEZONE('Europe/Belgrade', ts)
WHEN timezone = 'Europe/Berlin' THEN CONVERT_TIMEZONE('Europe/Berlin', ts)
WHEN timezone = 'Europe/Bratislava' THEN CONVERT_TIMEZONE('Europe/Bratislava', ts)
WHEN timezone = 'Europe/Brussels' THEN CONVERT_TIMEZONE('Europe/Brussels', ts)
WHEN timezone = 'Europe/Bucharest' THEN CONVERT_TIMEZONE('Europe/Bucharest', ts)
WHEN timezone = 'Europe/Budapest' THEN CONVERT_TIMEZONE('Europe/Budapest', ts)
WHEN timezone = 'Europe/Busingen' THEN CONVERT_TIMEZONE('Europe/Busingen', ts)
WHEN timezone = 'Europe/Copenhagen' THEN CONVERT_TIMEZONE('Europe/Copenhagen', ts)
WHEN timezone = 'Europe/Dublin' THEN CONVERT_TIMEZONE('Europe/Dublin', ts)
WHEN timezone = 'Europe/Helsinki' THEN CONVERT_TIMEZONE('Europe/Helsinki', ts)
WHEN timezone = 'Europe/Istanbul' THEN CONVERT_TIMEZONE('Europe/Istanbul', ts)
WHEN timezone = 'Europe/Kiev' THEN CONVERT_TIMEZONE('Europe/Kiev', ts)
WHEN timezone = 'Europe/Lisbon' THEN CONVERT_TIMEZONE('Europe/Lisbon', ts)
WHEN timezone = 'Europe/London' THEN CONVERT_TIMEZONE('Europe/London', ts)
WHEN timezone = 'Europe/Madrid' THEN CONVERT_TIMEZONE('Europe/Madrid', ts)
WHEN timezone = 'Europe/Minsk' THEN CONVERT_TIMEZONE('Europe/Minsk', ts)
WHEN timezone = 'Europe/Oslo' THEN CONVERT_TIMEZONE('Europe/Oslo', ts)
WHEN timezone = 'Europe/Paris' THEN CONVERT_TIMEZONE('Europe/Paris', ts)
WHEN timezone = 'Europe/Prague' THEN CONVERT_TIMEZONE('Europe/Prague', ts)
WHEN timezone = 'Europe/Rome' THEN CONVERT_TIMEZONE('Europe/Rome', ts)
WHEN timezone = 'Europe/Sofia' THEN CONVERT_TIMEZONE('Europe/Sofia', ts)
WHEN timezone = 'Europe/Stockholm' THEN CONVERT_TIMEZONE('Europe/Stockholm', ts)
WHEN timezone = 'Europe/Tallinn' THEN CONVERT_TIMEZONE('Europe/Tallinn', ts)
WHEN timezone = 'Europe/Vienna' THEN CONVERT_TIMEZONE('Europe/Vienna', ts)
WHEN timezone = 'Europe/Vilnius' THEN CONVERT_TIMEZONE('Europe/Vilnius', ts)
WHEN timezone = 'Europe/Warsaw' THEN CONVERT_TIMEZONE('Europe/Warsaw', ts)
WHEN timezone = 'Europe/Zagreb' THEN CONVERT_TIMEZONE('Europe/Zagreb', ts)
WHEN timezone = 'Europe/Zurich' THEN CONVERT_TIMEZONE('Europe/Zurich', ts)
WHEN timezone = 'Japan' THEN CONVERT_TIMEZONE('Japan', ts)
WHEN timezone = 'Pacific/Auckland' THEN CONVERT_TIMEZONE('Pacific/Auckland', ts)
WHEN timezone = 'Pacific/Chatham' THEN CONVERT_TIMEZONE('Pacific/Chatham', ts)
WHEN timezone = 'Pacific/Galapagos' THEN CONVERT_TIMEZONE('Pacific/Galapagos', ts)
WHEN timezone = 'US/Central' THEN CONVERT_TIMEZONE('US/Central', ts)
WHEN timezone = 'US/Eastern' THEN CONVERT_TIMEZONE('US/Eastern', ts)
WHEN timezone = 'US/Michigan' THEN CONVERT_TIMEZONE('US/Michigan', ts)
WHEN timezone = 'US/Mountain' THEN CONVERT_TIMEZONE('US/Mountain', ts)
WHEN timezone = 'US/Pacific' THEN CONVERT_TIMEZONE('US/Pacific', ts)
WHEN timezone = 'US/Samoa' THEN CONVERT_TIMEZONE('US/Samoa', ts)
ELSE ts
END;
-- Example of usage :
select timezone_name ,ctz(timezone_name , now() ) as ts from sys.project.timezone_names
;