More than 5 years have passed since last update.

BigQueryでTIMESTAMPを５分単位で丸める

BigQuery

Last updated at 2019-05-20Posted at 2019-05-20

timestampを5分単位に丸める

例

2018-04-07 03:17:42 → 2018-04-07 03:15:00

SQL

SELECT
  my_timestamp AS org_timestamp,
  TIMESTAMP_TRUNC(TIMESTAMP_SUB(my_timestamp, INTERVAL MOD(EXTRACT(MINUTE from my_timestamp), 5) MINUTE) ,MINUTE) AS rounded_timestamp
FROM
  `table`

MOD(EXTRACT(MINUTE from my_timestamp), 5) →　分を抜き取り、5で割った数
TIMESTAMP_SUB(my_timestamp, INTERVAL N MINUTE) → ${N}分前。つまりこれで「5分で割ったあまりの数」分前のTIMESTAMPになる
TIMESTAMP_TRUNC( TIMESTAMP ,MINUTE) → 分に丸める

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up