Time Range Help

Hello, I need my graph to compare sessions from this month to last month for example, how does one go about doing this? I have played around with every single variable possible and intervals but we want it to change upon user selection of time range on the top right. I can write queries that force it such as -

SELECT $__timeGroup(date, ‘24h’), sum(sessions) as sessions_30d
FROM ourchart
WHERE date BETWEEN (CURRENT_TIMESTAMP - INTERVAL ‘60 day’) AND (CURRENT_TIMESTAMP - INTERVAL ‘30 day’)
GROUP BY date ORDER BY date

but this defeats the purpose of the dynamic possibility on the top right date range selection and ALSO it falls off the chart if not selected a larger range from the top right. How does one do this? I have tried with $__timeFrom() AND $__timeTo() also and the interval variable and the timeframe variable. Any help/advice appreciated, thanks.

Assuming you’re using MySQL here, could you try using the __unixEpochFrom() and __unixEpochTo() variables and do date-based arithmetic with those?

between $__unixEpochFrom() - (30 * 86400) and $__unixEpochTo() - (30 * 86400)

I’m pretty new to grafana myself so I don’t know it would work, but seems worth testing. Per the docs, __unixEpochFrom() and __unixEpochTo() “Will be replaced by the (start|end) of the currently active time selection”

http://docs.grafana.org/features/datasources/mysql/

Thank you so much! I’m using postgresql but its very similar, just tried this variation too and its a no go :frowning: I tried a few different variations of doing that and even adding a few more postgresql query codes near it but nothing seems to budge. Thank you!!

I should have used __unixEpochFrom() and __unixEpochTo() in order to work with the seconds. Edit my first reply.

Sorry it didn’t work!

No problem!! I appreciate any help :slight_smile: Yeah for my exact scenario the unixepochfrom/to doesnt work because our data is in timestamps also >_<

convert the time stamps to epoch? pretty simple with UNIX_TIMESTAMP() function in mysql

I am using postgreSQL but thanks

and when I try the postgresql method I get this error - pq: operator does not exist: timestamp with time zone >= double precision

1 Like