I have an external data source automatically inserting data into my BigQuery table, this data source includes a timestamp field which does not have a timezone connected to it, however, I know this timestamp is in the Europe/Amsterdam timezone. The problem here is that when this timestamp is inserted into BigQuery, BigQuery automatically defaults the timestamp to UTC, which it is not. And in my specific case, I want to convert this timestamp to UTC. However because BigQuery already defaulted the timestamp to UTC (while it is actually Europe/Amsterdam), I cannot easily convert it to the actual UTC timezone.

Is there any way to convert this timestamp, which BigQuery thinks is already UTC, to the actual UTC timezone within a query? I can't just give it a -02:00 offset due to Daylight Savings coming into play which changes this offset from 2 hours to only 1 hour depending on the time of year.

Any help would be appreciated, I have been kind of stuck on this :)

An example of the timestamp in BigQuery would be 2022-09-30 01:23:45 UTC

CodePudding user response：

There is probably a better way but this should work

with
input as (select timestamp("2022-09-30 01:23:45 UTC") as ts)

select 
  ts,
  timestamp(replace(cast(ts as string), ' 00', " Europe/Amsterdam")) updated_ts
from input