Joel Greijer

with  animal  as (    select   *   from        unnest ([ struct        ( 1   as  zoo_id,  'Horse'   AS  animal_name,  1   as  animal_count ) ,        ( 1 ,  'Cow' ,  25 ) ,        ( 2 ,  'Old World Swallowtail Butterfly' ,  1000   ) ,        ( 4 ,  'Fish'  , 5500 ) ,        ( 5 ,  'Beetle'  , 700000 )      ])   ) , zoo  as (    select   *   from        unnest ([ struct        ( 1   as  zoo_id,  'Skansen'   as  zoo_name,  100   as  city_id ) ,        ( 2 ,  'Fjärilshuset' ,  100   ) ,   ...

Converting iso dates with timezones to BigQuery timestamp can be a hassle if the date contains more than six decimals. select   cast ( '2023-03-21T06:49:19.1234567+00:00'   as   timestamp ) Invalid timestamp: '2023-03-21T06:49:19.1234567+00:00' One solution could be to truncate the fractional part like so create   or   replace   function  [your_dataset].truncate_iso_timestamp_string ( detailed_iso_timestamp  string ) /* big query crashes with more than 6 decimals on dates This function will truncate to 6 decimals and cast to timestamp. */ returns   timestamp as   (    cast (      concat (        left (          split ( detailed_iso_timestamp, '+' )[ offset ( 0 )] ,  26 ) ,          '+' ,    ...