Dear all,
I am importing data from MS SQL into Grafana.
I want to select time series in Grafana:
But I get error:
“db has no time column: no time column found”
Although I think I have proper time series data in MS SQL:
Any idea, what I am doing wrong?
If I import it is a table, it is imported correctly in the following format: 2022-02-16 17:01:22
What data type is that column? And what does your query look like?
Hey, it is datetime
Should it be “timestamp”?
Datetime is good. What is not working? And please share some your query and screen shot
Query is not working. I get an error: “db has no time column: no time column found”
The query I am running is: “SELECT timestamp, duration FROM NodeRed.dbo.switches”
I am new to time-series database concept. It is possible, I am missing a foundation.
Thank you for your help
In the meantime, I realized I was missing some fundamentals…I haven’t selected time value. After selecting value, query is executed successfully.
1 Like
I need some additional help here. And sorry for stupid/newbie question.
I am having data about my smart switched from my house. There is a lot of data. I would like to get sum calculation on hourly/daily/monthly basis.
I have this simple query, that is showing all the values…
SELECT
$__timeEpoch([timestamp]),
[friendlyName] as value,
[Duration] as metric
FROM
dbo.switches
WHERE
$__timeFilter([timestamp]) AND friendlyName = 'KristofMainLight'
ORDER BY
[timestamp] ASC
Now I would like to have sum per particular day.
How to achieve that?
so now your question is really more sql than grafana. So what have you tried?
Ok, I will try to be more precise. I am tracking data about duration time of my switches in my house. What I managed so far is that Gradana is displaying duration time of each switch. Below is example of one switch, but as you can see it is showing me values for each event. I would like to achieve that Grafana would show me horuly bar and in this case instead of four bars between 20:00-21:00 it would show me only one bar withe the sum duration of four.
I hope I was clear enough now 
I’m using this format on MSSQL for daily grouping:
select datediff(second, '1970-01-01', '2023-01-03 11:00:00.000') as time
I solved it. Here is the solution if anyone will need it:
SELECT DATEADD(month, DATEDIFF(month, '19000101', timestamp), '19000101') AS time, SUM(Duration) AS SumDuration
FROM dbo.switches
WHERE friendlyName='KristofMainLight'
GROUP BY DATEADD(month, DATEDIFF(month, '19000101', timestamp), '19000101')
ORDER BY DATEADD(month, DATEDIFF(month, '19000101', timestamp), '19000101')
3 Likes