Hi all, i have implemented my graph based on mysql datasource. Actually everything good except for the time range selection.
If i select last month, last week, today or anything else time range interval my graph is not updated. Can you say me how do that? Maybe must be added some configuration into the query?
This is a printscreen of my graph and query
Thank you
Cosimo
Hi cosimodelplatoacn,
The graph is not updated because you do not have the timeFilter in the Query, so you are bringing all the data from your database, regardless of when they were generated.
See docs
Hi @danielgonzalez, many thanks.
I have added the macro $__unixEpocFilter but I see empty graph
I have an analytics database generated by WSo2 and the timestamp column on database table is something like 1562939040000 instead Grafana Query Inspector trace something like this
data:Object
from:“1563516652108”
to:“1563520252108”
I guess i have a problem with the timestamp traced by wso2 that is not compliant to the timestamp expected into the grafana timefilter.
Any suggestion is accepted, many thanks for now.
Thank you
Cosimo
I ran into the same problem. I believe that if you expand the time range(backwards) you will start seeing data.
The data is there, but the time filter is cutting it off based on the time zone, here is it showing all of the data.
The problem is that the Query builder has:
date AS “time”,
…
$__timeFilter(date)
data AS “time” is getting in the data in UTC.
The $__timeFilter(data)
Translates to: FROM_UNIXTIME(), which returns the time in local time.
So instead I had to switch to editing the SQL directly and use:
$__time(date),
$__time also returns local time and this keeps the graph times, and filter times consistent.
I think this is because you do not have the time zone properly configured. Grafana has more precision in the timestamp, but in my opinion it is the same format.
And where would that configuration be?
If you are referring to this one:
If I have it set to Default (or set it to Local browser time) I get:
If I select UTC I get:
Which is “correct” but not what I want to see, I don’t want to see my data in UTC time (7:30).
I have changed the preference time stamp in UTC and Local Browser but still see empty graph.
tried with timeFilter and unixEpochTimeFilter.
Do you have any other suggesions? @mynameitsnot @danielgonzalez
Thank you
Cosimo
@cosimodelplatoacn did you see/try what I posted in my first comment above?
Note this is only valid if the time filter is shifting the data a few hours (the difference between my time zone and UTC), if that isn’t the problem then I don’t know what the problem is.
Here is a comparison of the exact same data using the query builder, and my change using the SQL editor.
The one marked as “Kitchen” is the correct graph.
And here is both queries where you can see the SQL that the query builder used.
date AS “time” and $__timeFilter(date) are shifted in time by one localizing the time and the other not.
By using:
$__time(date) and $__timeFilter(date) both are localized in the same way.
It would be on the MySQL server.
They should keep in mind that the time zone selector on the dashboard only refers to the display. that is, if you have “last 5 hours” and UTC time zone, it will be shown from UTC up to 5 hours ago on the X axis; If you have a local time zone, the last 5 hours from local time will be displayed.
but this time zone declares nothing about the origin of the data. I mean, this does not tell grafana if your data is in UTC or any time zone.
To tell grafana what time zone it is, you must properly configure the database and use a data type compatible with time zone, or make a conversion to UTC and give this to grafana. These conversions can be done using the native functions of MySql, sometimes it is easier to make a sum, for example 18000 seconds for GTM-5.
Well in my case one of the very first things I did is offset all my dates/times in my database thinking that was the problem. But in no case could I get the graph dates and the filter dates to line up when using date AS “time” and $__timeFilter(date). Only when I switched to __time(date) and __timeFilter(date) did the two line up.
Now mind you since __time(date) and __timeFilter(date) are both macros that run on the MYSQL server and could be interpreted differently depending on some setting in MYSQL it is certainly possible the behavior might be change able there. But if so it is quite strange since this is also a straight no change install of MySQL.
Note the problem I had was the inconsistent treatment of the date between date AS “time” and __timeFilter(date). It wasn’t a matter of not being able to shift the data on the graph to where I wanted it.
Actually i have grafana installed on the same machine where is the analytics database i am sure time are matching. I have configured Grafana with local time.
By using unixEpochFilter i see grafana query inspector display this query
sql:“SELECT AGG_TIMESTAMP AS “time”, userTenantDomain AS metric, AGG_COUNT FROM apirespathperapphttpcoderessizewithtenantlatencyaddress_minutes WHERE AGG_TIMESTAMP >= 1563822843 AND AGG_TIMESTAMP <= 1563995643 AND userTenantDomain != ‘carbon.super’ ORDER BY AGG_TIMESTAMP”
My concerns is that on my database timestamp are not like this format 1563995643 but are more digit like 1562939040000 Maybe for this reason doesn not matche the filter?
THank you @mynameitsnot @danielgonzalez
@cosimodelplatoacn
If the only thing you are concerned about is the difference between 1563995643 and 1563995643000 that isn’t a problem. Unix time (which your database is going to use) is in seconds from the epoch, and that is the 1563995643 number. Grafana uses a slightly different format to that it can have times in the milliseconds. So it adds the last three 000 (and they are removed when dealing with a database that doesn’t use them).
So depending on the context 1563995643 and 1563995643000 are exactly the same date/time.
That isn’t going to be your problem.
That they are in the same machine does not imply perse that they have the same time zone.
You could use something like this:
SELECT
AGG_TIMESTAMP/1000 AS “time”,
userTenantDomain AS metric,
AGG_COUNT
FROM apirespathperapphttpcoderessizewithtenantlatencyaddress_minutes
WHERE $__unixEpochFilter(AGG_TIMESTAMP/1000) AND userTenantDomain != ‘carbon.super’
ORDER BY AGG_TIMESTAMP
Thank you solved with this solution.