BigQuery Standard SQL UDF to apply a timezone offset to a timestamp

apply_timezone_to_timestamp.sql

-- Takes a timestamp (in seconds) and a timezone offset (eg +0730) and returns a timestamp
CREATE TEMPORARY FUNCTION applyTZOffsetToUnixTimestamp(ts INT64, tzOffset STRING)
RETURNS INT64 AS (
  CASE
    WHEN SUBSTR(tzOffset, 1, 1) = '+' THEN ts + (CAST(SUBSTR(tzOffset, 2, 2) AS INT64) * 60 * 60) + (CAST(SUBSTR(tzOffset, 4, 2) AS INT64) * 60)
    WHEN SUBSTR(tzOffset, 1, 1) = '-' THEN ts - (CAST(SUBSTR(tzOffset, 2, 2) AS INT64) * 60 * 60) - (CAST(SUBSTR(tzOffset, 4, 2) AS INT64) * 60)
    ELSE NULL
  END
);