Hello fellow Grafaners. Could someone please help me out with the issue I’m currently facing? Also, please do let me know if more info is required on my side. Thank you so much in advance.
-
What Grafana version and what operating system are you using?
I just installed Grafana like 3 days ago, literally, so I assume it’s the latest version available for Windows. Version is “Grafana v12.0.1 (80658a73c5)”. My OS is Windows 11. -
What are you trying to achieve?
My question is regarding Time Series Panel (not Bar chart, or anything else). I am trying to convert my time in the x-axis into YYYY-MM-DD format. -
How are you trying to achieve it?
My database is SQLite.
I have the following query script under Queries:
SELECT
strftime('%s', ls.Date) as time, -- This becomes the X-axis (date timestamp)
-- Convert 'Slept at' to seconds since midnight for numerical plotting on Y-axis
(CAST(strftime('%H', ls.StartSleepTime) AS INTEGER) * 3600) +
(CAST(strftime('%M', ls.StartSleepTime) AS INTEGER) * 60) +
(CAST(strftime('%S', ls.StartSleepTime) AS INTEGER)) AS "Slept at (seconds from midnight)",
-- Convert 'Awoke at' to seconds since midnight for numerical plotting on Y-axis
(CAST(strftime('%H', ls.EndSleepTime) AS INTEGER) * 3600) +
(CAST(strftime('%M', ls.EndSleepTime) AS INTEGER) * 60) +
(CAST(strftime('%S', ls.EndSleepTime) AS INTEGER)) AS "Awoke at (seconds from midnight)",
ls.TotalSleepHours as "Total sleep hours",
ls.TotalSleepHours * 3600 as TotalSleepInSeconds,
strftime('%d日', ls.Date) as DateLabel
FROM Sleep_Table ls
WHERE strftime('%Y-%m', Date) = strftime('%Y-%m', 'now');
My Date field in my SQLite database is just a text (even though I set it to Date data type in SQLite DB, SQLite doesn’t really have such a type, thus it’s being treated as a text type, correct me if I’m wrong on this part though) with the following sample data: “2025-06-19 00:00:00”. The time, which I have no intention in tracking for this Date field, always contains 00:00:00. Now, I just can’t simply use this field in Grafana’s Time series panel, since they’ll complain it’s not a proper time format field. Because of this, I’m forced to use strftime('%s', ls.Date) as time in my query. Someone please correct me on this if I’m doing this all wrong, or my approach is incorrect.
- What happened?
As you can see in the screenshot above, along the X axis is showing Time (date is being truncated away for god knows what reason it is).
If I were to switch it to table view (still within Time series Panel), it’s showing the following:
Now something I found it really weird, is that the original data, which is supposed to be 00:00:00, became 09:00:00. No idea why, but it’s happening.
I’ve attempted the following method:
But it’s still not working. Value still left unconverted
However, in the tooltip view, it’s correctly converted. You can see it right at the top “2025-06-19”, without showing the time, only the date:
-
What did you expect to happen?
I expect time field to be converted, from date time into a date only field, being shown along the x-axis.
Something like what others were able to achieve:
Original source: Time series Unit
-
Did you follow any online instructions? If so, what is the URL?
-
- https://community.grafana.com/t/how-to-convert-timestamp-and-then-group-by-date/65948/18: Tried using Transformations to convert the time field, once and twice, but still doesn’t work.
-
- http s://community.grafana.com/t/timeseries-x-axis-time-axis-complete-date-format/116564: (I was told that new users could only put two links in a new thread, so I’m trying to work around it by adding a space in this link here. When copy-pasting, please remove the whitespace in the link here.)
Unsolved, same case like mine.
- http s://community.grafana.com/t/timeseries-x-axis-time-axis-complete-date-format/116564: (I was told that new users could only put two links in a new thread, so I’m trying to work around it by adding a space in this link here. When copy-pasting, please remove the whitespace in the link here.)
Highly appreciated for the assistance.