CODEWITHSUNDEEP
sqlgoogle-bigquery
Pandafreak
Pandafreak

Reputation: 107

Group timestamp information by date and hour in Bigquery

I have this timestamp metric which shows the following information: 2021-08-30 22:10:22.838 UTC

I would like to split and group this info by date and hour, so it should look something like this in BQ:

Date: 2021-08-30 Hour: 22:00:00 UTC

Anyone know how do do this? Thanks!!

Upvotes: 0

Views: 929

Answers (1)

user45392
user45392

Reputation: 622

To extract the date and hour you can use this (replacing your_ts with the appropriate field name).

SELECT 
  EXTRACT(DATE FROM your_ts) dt,
  EXTRACT(HOUR FROM your_ts) hr
FROM tbl

If you want to keep the formatting you provided (returning strings), you can try something like this.

SELECT
  FORMAT_TIMESTAMP("%F", your_ts) dt,
  FORMAT_TIMESTAMP("%X", TIMESTAMP_TRUNC(your_ts, HOUR))
FROM tbl

Upvotes: 2

Related Questions