group all readings by day in BigQuery

user_9999393 0
Viewed 4481

I am new to BigQuery and so I'm struggling with grouping my data in a way that could be useful. At this point in time I have converted the unix timestamps to a date ( e.g 2018-08-27 04:54:56 UTC, etc...). I have readings every 10 minutes or so and would like to group them by date only and then take the daily average for my various columns. I think 'group by' and then'avg' might be the way forward but I am unsure how to implement the first part. The code I have used so far to create my table is as follow:

  SELECT TIMESTAMP_SECONDS(timestamp) as timestamp,humidity,co2,temperature,app_id
  FROM data.staging 
  where timestamp is not null
  ;

and here are a few rows of what my output looks like:

|| Timestamp              || humidity  || co2   || temperature  || app id||

||========================||===========||=======||==============||========||

||2018-08-31 13:35:50 UTC || 63.4      || 634.5 || 21.2         || office_||

||2018-08-31 14:37:20 UTC || 67.8      || 600.4 || 20.8         || office_||

etc..

Sorry for the poor formatting and if there's anything else needed I would be happy to provide. Thanks for any help!

google-bigquery group-by timestamp average sql
1 Answers

You can convert the timestamp to a date and then use GROUP BY:

SELECT DATE(TIMESTAMP_SECONDS(timestamp)) as dte, AVG(humidity), AVG(co2), AVG(temperature)
FROM data.staging 
WHERE timestamp is not null
GROUP BY dte
ORDER BY dte;

Note that the date is the UTC date. DATE() takes a second argument. I often call it as:

SELECT DATE(TIMESTAMP_SECONDS(timestamp), 'America/New_York') as dte,
user_11440350
Related