CODEWITHSUNDEEP
sqlgoogle-bigquery
Ben P
Ben P

Reputation: 3369

Rounding a timestamp to 15 minute intervals in LegacySQL

I am pulling some data showing average interactions by hour, but I would like to group this so that it shows me average interactions per 15 minute blocks using a timestamp, unfortunately I am restricted to LegacySQL. I've read several solutions for achieving this with StandardSQL, but how could I do it with Legacy?

Here is my code so far:

SELECT
DAYOFWEEK(meta_recordDate) AS day_num,
HOUR(property_event_ts)+1 AS hour,
IF(page_type = 'transaction',COUNT(transaction_id),0)/4 AS average_interactions,
FROM [TABLE]
WHERE (TIMESTAMP(meta_recordDate) BETWEEN DATE_ADD(TIMESTAMP(CURRENT_DATE()), -31, 'DAY') AND DATE_ADD(TIMESTAMP(CURRENT_DATE()), -1, 'DAY')) 
AND dayofweek(current_date()) = DAYOFWEEK(meta_recordDate)
AND page_type = 'transaction'
GROUP BY page_type,day,hour,day_num
ORDER BY hour

My data from this query looks like this:

enter image description here

Upvotes: 0

Views: 414

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173036

To round minutes of a timestamp to 15 minute intervals in LegacySQL - you can use below expressions

15 * INTEGER(MINUTE(property_event_ts)/15 + 1)

So, add 15 * INTEGER(MINUTE(property_event_ts)/15 + 1) AS mnt into your SELECT list and mnt to GROUP BY list

Depends on which way you want to round you can potentially remove + 1 portion

Upvotes: 1

Related Questions