Offset for macro $__timeGroup


Is it possible to create an offset for the macro $__timeGroup to choose the first day you want to display on the graph ?
When you group by month for example, the graph display a random day of the month…

Thank you

1 Like

I recently wrote a blog post about the use of SQL query macros and how they expand, and how they interact with time windows, I’d recommend checking it out. But I’m going to get to your question:

First thing to do is make sure to use “inspect query” as described in that article to verify. you know how that macro is getting expanded into actual SQL. Usually it’s going to get expanded into something like this: UNIX_TIMESTAMP(payment_date) DIV 3600 * 3600

So if you wanted to create an offset, the key thing to understand is that’s UNIX seconds since the epoch, and you could offset it by adding or subtracting any number of other seconds. (Do the math to add a day, and so on).

But be careful. Although you can do this, make sure it’s the right thing to do. Thing is, most people are using these macros to make sure that the “time window” expressed in the dashboard lines up with the query going to the data source. If you put an extra offset in here, users of your dashboard might be mislead: let’s say the dashboard says it’s showing last 6 hours, but actually you’ve modified the offset and it’s showing last 5 1/2 (for example). So while you could do this, I’d advise just care to make sure you understand how that’s going to interact with the dashboard time selector, and whether those various combinations will make sense. If they do for your use case, go for it.

Hope this helps

Hello, thanks for your reply ! For example, if i would group by month (start 1st of the month) using the macro $__timeGroup, how could i manage to do this ?

1 Like

Apologies, I had some errors in my initial reply. A colleague told me this:

For and evaluation interval are different. The
evaluation interval is how often the rule is evaluated, and for is how long the condition must be exceeded before the rule is considered firing

That being said I don’t know how it’s specified via the API yet, I will try to follow up once I can find this.