Skip to content

Instantly share code, notes, and snippets.

@doit-mattporter

doit-mattporter/window_opened_closed_utc.sh

Created February 5, 2021 23:25
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save doit-mattporter/e0081b981de1c093315fe27df79ca420 to your computer and use it in GitHub Desktop.
Save doit-mattporter/e0081b981de1c093315fe27df79ca420 to your computer and use it in GitHub Desktop.
Download ZIP
Create a table of open & closed window timeframe datetimes in UTC
Raw
window_opened_closed_utc.sh
# Convert table of open and closed window timeframes to UTC
bq query --nouse_legacy_sql \
"CREATE OR REPLACE TABLE ${PROJECT_ID}.sensordata.window_opened_closed_utc AS (
SELECT
DATETIME(TIMESTAMP(CONCAT(DayPST, \"T\", FORMAT_TIMESTAMP(\"%H:%M:%S\", PARSE_TIMESTAMP(\"%r\", StartTimePST))), \"America/Los_Angeles\"), \"UTC\") AS StartTime,
DATETIME(TIMESTAMP(CONCAT(DayPST, \"T\", FORMAT_TIMESTAMP(\"%H:%M:%S\", PARSE_TIMESTAMP(\"%r\", EndTimePST))), \"America/Los_Angeles\"), \"UTC\") AS EndTime,
ObjectCode
FROM ${PROJECT_ID}.sensordata.window_opened_closed
)
"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment