I am within 3 weeks of first meeting Google Data Studio (DS). I am head of data at a small firm that was subsumed into a larger firm last year. Now that the initial merge issues are clearing, we’re getting down to the brass tacks of making our very different technologies work together. They’re very google-oriented – lots of BigQuery & Go and the data bods use R. I’m more MySQL and Python myself. We didn’t have a regular BI tool, hence why I’m getting up to speed with DS atm. DS gives a lot of bang for your buck (it’s free!), but has some interesting quirks.

I’ve had a few other battles with DS already, hopefully I’ll get to expand on those in further articles. Some of these are real head-scratchers; things you would think are such obvious requirements that the lack of them certainly makes me think my dearth of experience is the issue. Such a one seems to be the inability to filter values by time. An initial internet search turned up a couple of hits, but nothing really concrete:

• This StackOverflow question from 7 months ago
• And this data studio community question from 18 months ago

So! How I can filter a set of timestamped results to show only values from the past hour?

I’ve inserted a Chart > Scorecard and I want to sum up the values in column 2 if the time in column 1 falls within the past hour.

DS has grand tools if I want to filter by the date:

But it seems only to let you set the date, the time of day is not adjustable. Minimum “Unit” granularity is “day”; even when my data type is Date & Time or Date Hour Minute.

Okay, so now what? I’ll try to make a different kind of filter – based on the time value

So, it starts out looking hopeful, I can choose the time metric and only include times that are later than a date and time I pick! But on closer inspection, that’s a fixed time, not a relative one, so no dice. Clicking around, nothing else in the filter allows relative comparison, so I’ll have to think of something else.

Hmmm. What about if I set an is_in_past_hour boolean? I’ll make a new calculated field. Oh, and by the way, DS doesn’t do IF statements. It’s case or nuffink.

After a bit of jiggery-pokery, including

case when datetime_diff(current_datetime(),the_time,HOUR)=0 then true else false end

erroring with Invalid formula – Invalid input expression. – WHEN conditions must compare a dimension or metric with a literal value.

I ended up ditching the case statement altogether and made an hours_before_now variable instead. I will filter results so that only results where this variable is 0 are included.

datetime_diff(current_datetime(),the_time,HOUR)

Phew. All done. But hang on, why are the results still not matching what I’m expecting?

Rounding.

It’s rounding half hours up or down.

Le sigh.

So I switch all the references in my filters and variables from hours to minutes and check there are fewer than 60 of them.

Finally, this works. I’m getting the same value in the DS report as this simple wee query in MySQL:

select sum(my_value) 
from my_table 
where the_time >= date_add(now(), interval -1 hour)
;

Time (out) for a coffee.