SWITCH_OFFSET_UDF

Description

This custom UDF is added to return a datetime offset value that is changed from the stored time zone offset to a specified new time zone offset.

Custom UDF overloads

Parameters

1. source_timestamp: A TIMESTAMP_TZ that can be resolved to a datetimeoffset(n) value.

2. target_tz: A varchar that represents the time zone offset

UDF

CREATE OR REPLACE FUNCTION PUBLIC.SWITCH_OFFSET_UDF(source_timestamp TIMESTAMP_TZ, target_tz varchar)
  RETURNS TIMESTAMP_TZ
  LANGUAGE SQL
  AS 
  $$
    WITH tz_values AS (
    SELECT 
        RIGHT(source_timestamp::varchar, 5) as source_tz,
        
        REPLACE(source_tz::varchar, ':', '') as source_tz_clean,
        REPLACE(target_tz::varchar, ':', '') as target_tz_clean,
        
        target_tz_clean::integer - source_tz_clean::integer as offset,
        
        RIGHT(offset::varchar, 2) as tz_min,
        PUBLIC.OFFSET_FORMATTER(RTRIM(offset::varchar, tz_min)) as tz_hrs,
        
        
        TIMEADD( hours, tz_hrs::integer, source_timestamp ) as adj_hours,
        TIMEADD( minutes, (LEFT(tz_hrs, 1) || tz_min)::integer, adj_hours::timestamp_tz ) as new_timestamp
        
    FROM DUAL)
    SELECT 
        (LEFT(new_timestamp, 24) || ' ' || target_tz)::timestamp_tz
    FROM tz_values
  $$;

-- ==========================================================================
-- Description: The function OFFSET_FORMATTER(offset_hrs varchar) serves as
-- an auxiliar function to format the offter hours and its prefix operator.
-- ==========================================================================  
CREATE OR REPLACE FUNCTION PUBLIC.OFFSET_FORMATTER(offset_hrs varchar)
  RETURNS varchar
  LANGUAGE SQL
  AS
  $$
   CASE
       WHEN LEN(offset_hrs) = 0 THEN '+' || '0' || '0'
       WHEN LEN(offset_hrs) = 1 THEN '+' || '0' || offset_hrs
       WHEN LEN(offset_hrs) = 2 THEN
            CASE 
                WHEN LEFT(offset_hrs, 1) = '-' THEN '-' || '0' || RIGHT(offset_hrs, 1)
                ELSE '+' || offset_hrs
            END
        ELSE offset_hrs
    END
  $$;

SQL Server

Query

SELECT 
  '1998-09-20 7:45:50.71345 +02:00' as fr_time,
  SWITCHOFFSET('1998-09-20 7:45:50.71345 +02:00', '-06:00') as cr_time;  

Result

SELECT 
  '1998-09-20 7:45:50.71345 +02:00' as fr_time,
  SWITCHOFFSET('1998-09-20 7:45:50.71345 +02:00', '-06:00') as cr_time;  

Snowflake

Query

SELECT
  '1998-09-20 7:45:50.71345 +02:00' as fr_time,
  PUBLIC.SWITCH_OFFSET_UDF('1998-09-20 7:45:50.71345 +02:00', '-06:00') 
  /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'SWITCH_OFFSET_UDF' INSERTED. ***/ 
  as cr_time;

Result

fr_time                         cr_time
------------------------------- ----------------------------------
1998-09-20 7:45:50.71345 +02:00 1998-09-19 23:45:50.7134500 -06:00