-1

I am streaming inserts into bigquery from adwords javascript. The javascript time stamp that gets inserted into the bigquery table as a string - "Sun Sep 06 2015 02:42:54 GMT-0700 (PDT)". How do i convert this string into a timestamp of format "2015-09-06 02:42:54" in a bigquery select statement.

Raina
  • 27
  • 1
  • 2

1 Answers1

1

You can do this by parsing out each part of the date (year, month, day, etc.) using the REGEXP_EXTRACT function, then constructing a string to pass into the TIMESTAMP function. You can check out the query reference for more info on these functions. See also: a similar question with a different initial date format.

Note however that BigQuery TIMESTAMPs are stored as UTC times, so you may want to shift the time according to the timezone in the string.

Community
  • 1
  • 1
Danny Kitt
  • 3,241
  • 12
  • 22