Why Timeseries data from MS SQL is not recognized as time in grafana

Grafana Cloud
1

Dear all,
I am importing data from MS SQL into Grafana.
I want to select time series in Grafana:
image
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:
image
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

1 Like
2

What data type is that column? And what does your query look like?

3

Hey, it is datetime
image
Should it be “timestamp”?

4

Datetime is good. What is not working? And please share some your query and screen shot

5

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

6

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
7

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?

8

so now your question is really more sql than grafana. So what have you tried?

9

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 :slight_smile:

image
image3024×824 45.1 KB

image
image3053×785 69.7 KB

10

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

image
image1920×902 89.8 KB

11

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