BigQuery: invalid time zone with UTC+5.5

April 17, 2023

The data we have involves a Time column and a timezone column. Unfortunately, some of the timezones are UTC-offsets that are funky, as below:

SELECT DATETIME(TIMESTAMP '2008-12-25 15:30:00', "UTC+5.5") AS my_time;

Invalid time zone: UTC+5.5

Removing the 0.5 works, but this is not ideal…

SELECT DATETIME(TIMESTAMP '2008-12-25 15:30:00', "UTC+5") AS my_time;
2008-12-25T20:30:00

Any thoughts on how to convert a timestamp to a timezone aware timestamp with a 0.5 Offset?

>Solution :

You might consider below.

-- sample data
WITH sample_data AS (
  SELECT TIMESTAMP '2008-12-25 15:30:00' time, "UTC+5" timezone
   UNION ALL
  SELECT TIMESTAMP '2008-12-25 15:30:00' time, "UTC+5.5" timezone
)
-- query starts here
SELECT SAFE.DATETIME(time, REGEXP_REPLACE(timezone, r'\.5$', r':30')) my_time
  FROM sample_data

-- query result
+---------------------+
|       my_time       |
+---------------------+
| 2008-12-25T20:30:00 |
| 2008-12-25T21:00:00 |
+---------------------+