Show timestamp modulo 24h on x axis

I want to show a time series value, but in a way that overlays the value pattern over the course of each day, i.e. resulting in as many curves as days in the time range.

I guess the usual Time series visualization does not support this, and the Heatmap features a different kind of diagram, with the time of day on the y axis and the bucket frequency as “color axis”.

Any ideas?

I really think that you would benefit from our How to use the Grafana forums - #2 It’s because I really didn’t understand what you need.

But from the gist of it, I would say that maybe using multiple queries and overriding the time data o each one would be a way to try to achieve something like you described.

1 Like

TBH, I don’t really understand what you mean by “benefit from our How To”. Maybe I am not able to express my question appropriately because English is not my mother tongue.

My problem is that I want to visualize the electricity price, which changes each hour in my power-supply contract. The query time range could be e.g. the last 30 days. The diagram would then show 30 curves, each indicating one day of the range. The x axis would go from 00:00 to 24:00, with no date.

A similar task would be visualizing outdoor or room temperature curves that are underlying a similar daily cycle. Same for any measurement that has some cyclic behaviour, be it daily, weekly, yearly or whatever.

So, the basis for that diagram would be a single query / variable. How would I split this in multiple queries (one for each day in the time range, if I understand your suggestion correctly) and override each query’s time range by shifting it to a common start time?

Just use the question/answer template on the topic. It’ll greatly help you explain your case.

In SQL I’d try to sum the value column, do a “date extract” on the day, another “date extract” of the hour both in the time column. Then I’d try to “group by” this “date extract” by the hour summing the values of the column with the values data.

If your skill in SQL isn’t enough, or you can’t format your data enough because it’s format on the datasource side, try to play arround with Transform data | Grafana documentation even more specific, try to play arround with Prepare Time Series. It’ll help you a lot.

I wish you good studies.

Not sure if we understand each other. I’ll try to consider some of the hints in your “How To” link, as far as they apply.

I am using Grafana 10.3.3, data coming from InfluxDB 1.8.10. My data is basically a simple time series with a timestamp and a numerical value for each data point. The InfluxDB timestamp is representing a point in time that can be split into a date and a time-of-day component in the context of a timezone setting.

Starting with a basic time series visualization, this shows a single curve of the values as Y values, timestamps as X values.

If, for example, the time range covers 30 days, I now want to “wrap” or “fold” the X range in a 30-fold way. The new X range would then go from 00:00 to 24:00 independent of the date. There would be the curve plot for the 1st day, 00:00–24:00, another curve for the 2nd day, 00:00–24:00, and so on, for all 30 days.

The effect is the graphic overlay of all (30) day-long value patterns, visualizing common trends and deviations.

A more advanced feature could be changing the curve color depending on the day index which allows discovering long-term trends.

No summation (or other aggregation) of values (Y axis) as mentioned by you, just “modulo 24h” on the timestamps (X axis). I cannot find a suitable transformation that does this. I also doubt the standard “Time series” visualization supports this kind of operation resulting in multiple curves using the same X range. Maybe an XY plot could do a part of the task, but the progression context would be lost without lines. I also have no way to get the timestamp / time of day as an InfluxDB query.

I just found an example: Have a look at this article. The first illustration is what I am intending to do, only that they are plotting it over a one-year cycle instead of a one-day. They also use the color gradation for the year.

Well. I’m really sorry. But this is as far I can try to guide you. I believe I understand what you want, but you need to review what I’m trying to tell you.

Anyway, there’s other members here on the forum, let’s see if any of them could share some interpretation and ideas.

Hope you achieve what you’re aiming for!

Ok, as I supposed I found a solution using a plugin. Plotly can be scripted to generate the diagram I need. In a first attempt, this is the result:

Of course this still needs fine tuning, but it aims in the right direction.

Nevertheless I think this type of cyclic diagram could be provided as a standard option.