CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquerylooker-studio
Saverio Terracciano
Saverio Terracciano

Reputation: 3915

How to plot a time series from BigQuery with a minute accuracy?

I have a dataset in BigQuery, and I am using it as a source to create a report on Data Studio.

I can plot the data as a time series / combo / line chart using the Timestamp as dimension and other numeric fields I have as metrics.

The issue I have is that the most I can get as accuracy is down to the hour, while I need a minute accuracy. The dataset in BigQuery has an accuracy of milliseconds, but I don't seem to be able to get it in Data Studio.

I have read short suggestions about creating a string with the date and using that as metric, or extracting and combining a minute field, but I can't seem to be able to make it work.

samp

Upvotes: 3

Views: 3270

Answers (2)

Felipe Hoffa
Felipe Hoffa

Reputation: 59275

Update 2019: minute resolution is now natively supported!

In BigQuery, TRUNCATE by MINUTE and GROUP BY it. Then in Data Studio set this timestamp as STRING value instead of a DATE. Create a line chart with this timestamp STRING as the X dimension, and voila.

enter image description here enter image description here

Query for this vis:

WITH data AS (
  SELECT bus, ST_GeogPoint(longitude, latitude) point
    , PARSE_TIMESTAMP('%Y%m%d %H%M%S',FORMAT('%i %06d', day, time)) ts
  FROM `fh-bigquery.mta_nyc_si.201410_bustime`
  WHERE day=20141014
  AND bus IN (7043, 7086, 7076, 2421, 7052, 7071)
)

SELECT bus, TIMESTAMP_TRUNC(ts, MINUTE) ts, AVG(distance/time) speed
FROM (
  SELECT bus, ts
    , ST_DISTANCE(point, LAG(point, 3) OVER(PARTITION BY bus ORDER BY ts)) distance
    , TIMESTAMP_DIFF(ts, LAG(ts, 3) OVER(PARTITION BY bus ORDER BY ts), SECOND) time
  FROM data
)
WHERE time IS NOT null 
GROUP BY bus, ts
HAVING speed < 500

Upvotes: 3

Juta
Juta

Reputation: 411

You can create a view of you table using this query:

SELECT
  t.*,
  FORMAT_TIMESTAMP("%Y-%m-%d %H:%M", t.timestamp) as date_time
FROM
  `project-id.dataset-id.table-id` AS t

After creating such view, you can add this view as your data source and use the field date_time for your time series

Upvotes: 0

Related Questions