SQL – Creating UTC Timestamp from separate date/time/timezone information

I’m working with BigQuery and have a table that looks like:

Where local time is given by hhmm. I’m wondering if I can format this information into a timestamp column in SQL that yields time in UTC.

I know I can use `TO_TIMESTAMP` but that would involve concatenating all these columns as strings first. Is there any better way to do this? If I were to concatenate, I’m not sure how I would use timezone and then back out UTC.

>Solution :

You might consider below.

WITH sample_table AS (
    SELECT 2015 year, 6 month, 24 day, 'America/Los_Angeles' timezone, 1930 local_time
)
SELECT TIMESTAMP(DATETIME(year, month, day, DIV(local_time, 100), MOD(local_time, 100), 0), timezone) utc
  FROM sample_table;

• DATETIME(year, month, day, hour, minute, second)

Query results