How do I get a weekly time series on Grafana?
I am using MariaDB and Grafana to keep track of weekly consumption totals, but Grafana does not recognize this date format "DATE_FORMAT(`Timestamp`, '%Y-%u') AS Week" as a time field.
Any suggestions? The query is successful on MariaDB.
Other failed SQL alternatives
- DATE_ADD(DATE_FORMAT(`Timestamp`, "%Y-%m-%d 00:00:00"), INTERVAL(-WEEKDAY(DATE_FORMAT(`Timestamp`, "%Y-%m-%d 00:00:00"))) DAY)
- WEEKOFYEAR(`Timestamp`) AS Week
- YEARWEEK(`Timestamp`) AS Week
Other failed Grafana alternatives
- Format time transformation
- An XY Chart, but the "Week" field is not available for selection.
](https://i.sstatic.net/BHAYNC6z.png)
Thank you in advance.
Solution
I got the answer on my crosspost on Grafana Forums.
STR_TO_DATE(CONCAT(YEAR(`Timestamp`),' ', WEEK(`Timestamp`, 3),'1'), '%X %V %w') AS Week
This did the trick. It is a compromise that works.
Unfortunatelly, it is not ideal, because the format of the original query would be way better for visualization, but I guess that is where Grafana is as of today.
Also, the fact that the error message goes “Data is missing a time field” instead of “Incorrect time field format” is confusing.
- How do I get a weekly time series on Grafana?
- How to customize title, axis labels, etc. in a plot of a decomposed time series
- Optimal data structure for time-series analysis
- SQL count of previous occurrences of a value in a time series
- Identify primary and replica on QuestDB
- Convert LT JOIN query to LAG
- Correlation returning unexpected results in QuestDB
- TLS with PgBouncer for QuestDB. Server refused SSL
- Can't enable QuestDB wal on my instance in MacOS or Docker
- How do you properly format data for CNN classification?
- Gnuplot plots incorrect time for time series plot
- Why not use a HashMap to get the exact enum instead of comparing it one by one?
- Select a range time using epoch in QuestDB
- merging irregular time ser
- Can the designated timestamp syntax using TIMESTAMP keyword automatically sort by ts?
- Compare DOUBLE columns with exact ZERO value in QuestDB
- handling nested data efficiently
- Volume Spike Detector
- Polars query to QuestDB results in "query is not allowed here"
- Math operations with lag()
- ASOF JOIN query timing out
- "Error - Constant Expected" when I do an IN subquery
- Materialized view invalidated and cannot remember original SQL
- Cannot create a materialized view for the _query_trace table
- Importing CSV with timestamps in milliseconds
- Could not flush buffer: http://localhost:9000/write?precision=n: Connection Failed: Connect error: Can’t assign requested address (os error 49)
- Calculating Bollinger Bands with QuestDB
- Is there a way to get p,d,q,P,D,Q params from statsforecast AutoARIMA
- Why are random tables created?
- How to compute and expand average power over time?