Hi all,
I’ve just created a new dashboard but I can’t plot the data.
I’ve imported from a CSV like that:
Time (date),495 #2 3P L1-L3 E[kWh] (numeric),495 #2 3P L1-L3 P[kW] (numeric),495 #2 3P L1 P[W] (numeric),495 #2 3P L2 P[W] (numeric),495 #2 3P L3 P[W] (numeric)
2019-07-08 15:25:00,0.64,10.95,4139.20,3483.29,3324.24
2019-07-08 15:30:00,1.30,9.94,3683.09,3217.77,3037.69
I’m querying the DB like that:
SELECT
Time AS time,
`495 #2 3P L1-L3 P[kW]` AS 3OG_BT2_kW
FROM `WSE_KA-AIK_Wurth 3. OG_WAGO`
ORDER BY time ASC
On the dashboard I only see “No data points”, if I look into Explore the data looks like this:
1562599500000 10.95
1562599800000 9.94
1562600100000 10.37
I don’t get why the timestamp gets modified into this, I hope one of you has a hint for me…
The 1562599500000 is the Unix time stamp, which is in the number of seconds since January 1, 1970.
Internally Grafana is using this kind format for time (as do many database programs) which takes up less space and makes time comparisons possible in a single test unlike the human readable forms.
Now on to what I believe is your real problem because I ran into it myself. Most likely your data is not showing up because your dates are in local time, and Grafana wants them in UTC time, and the default time range for the graph is the last 6 hours. For me being in California the offset is 7 hours, just outside of what it was displaying. Switching the “Today” for the graph range showed my data, shifted by the 7 hours.
If this is your problem you have a couple choices. One is to convert your dates to UTC before putting them in your database.
The other is to convert them to UTC in the Grafana query.
SELECT CONVERT_TZ( NOW(), @@session.time_zone, ‘+00:00’ )
NOW() above would be changed to your date field in your database.
Note I just noticed that the time stamp has a few extra zeros.
A normal Unix time stamp for a date around now would have 10 digits like this:
1562599500 (which BTW is Mon Jul 8 08:25:00 2019 Pacific time).
The extra 000 must be used if it wants to go into milliseconds.
Thank’s for your reply, but how can I avoid to have grafana added the milliseconds to the timestamp.
My Explore looks like this:
And the the Data in MySQL like this:
You don’t want to get rid of the milliseconds, that is the format that Grafana needs it in.
Note the milliseconds are only the last 000 in the number the rest is the date and time in Unix time format, as I explained above.
Your SQL statement is incorrect for the time. Here is an example of what I get when I just go into the Explorer. Notice the use of the UNIX_TIMESTAMP() function.
And when I look at what they have created in the dashboard it looks like this.
Notice that they have switched to using “time” instead of time_sec, which is the “future preferred” way of doing it.
This is as closed as I could get to the generated SQL
SELECT
UNIX_TIMESTAMP(Time) DIV 600 * 600 AS “time”,
495 #2 3P L1-L3 P[kW] AS metric
FROM WSE_KA-AIK_Wurth 3. OG_WAGO
WHERE $__timeFilter(Time)
GROUP BY 1
ORDER BY UNIX_TIMESTAMP(Time) DIV 600 * 600
But still I don’t get a plot, I sure see data, but even if the data is inside $__timeFilter() I don’t get my graph
Why are you mixing the two formats?
And why are you dividing the numbers?
I guess because nothing is working for you.
As far as I can tell it is:
Select UNIX_TIMESTAMP(Time) time_sec, …
OR
Select Time as “time”, …
If these formats don’t do it, then I don’t know what is going on.
Sorry. Note that time numbers like:
1562957244000
Are correct.
Note here is a Unix timestamp converter:
https://www.unixtimestamp.com/index.php
Where am I mixing the two format’s? I’m using time and not time_sec.
And using DIV only because it was suggest like that by the builder, but it works nearly the same without DIV, just give’s me some more value’s. But generally yes: nothing works.
And before I had created this post I tried a unixtime conversion and got this:
Which seemed to be damn wrong for me 
Sorry about all this. I started this thread thinking that you had the same problem I had, which is that my datetimes in mysql were in local time, so they were shifted off my graph that was only for 6 hours.
Then I got looking at what your query was in comparison to what I was seeing and got off on that tangent forgetting that in fact you do you good time stamps reporting in the Explorer.
As for the “mixing”
UNIX_TIME(var) goes with as time_sec
and var goes with as “time”
You had:
UNIX_TIMESTAMP(Time) as time
Anyways on the time stamp. As I was trying to explain (badly I guess), they are using a “modified” Unix time stamp. As in UnixTimeStamp with milliseconds added to the end.
To get the right time to put in to the Unix converter, remove the last 3 zeros:
So for your timestamp above:
1562957244 which is:
Just be clear here is a screenshot of some of my data, notice the dates.
No reason to excuse, I’m an hour or two of as I’m in CET…
But yeap my issue isn’t the time gap, I’m just not seeing a graph from the data in my DB
Here is another shot at it. I noticed a couple of things about your graph.
First off when it doesn’t have any data it will say “No Data” in the middle of the graph.
Yours doesn’t have that, so it is picking up “something”.
Next I don’t see the series legend, which should be there.
So I tried various things to get the same results and here is what I got.
The way I got this is col #2 is VARCHAR(20).
So maybe the problem is with the data type you used for your values.
Here is me switching to col #1 which is FLOAT in the database.
One my piece of information. I was changing things up today and accidentally put the data in the wrong year. And the graph shows this message for that problem:
So if the real problem had been the timestamp I think you would have seen this.
That means the problem has to be the data value/data type.
