Problem with $__timeFilter() MSSQL

Grafana Dashboards
1

Hello,

I’m using Grafana 11.0.0 with an MSSQL database

I have a problem with the use of $__timeFilter(), in fact it doesn’t give the right date at all to filter the data no matter what time zone you choose. There’s always a 2h offset (I’m in UTC+2).
Here are 3 identical requests (except one where I add the $__timefilter()) and the results are not identical:

Request in MSSQL :
image

Requests in Grafana with UTC+0 :

image
image1515×736 49.2 KB

Requests in Grafana with UTC+2 :
image
image1495×769 52 KB

The correct result is obviously on MSSQL, but I don’t understand why the $__timeFilter() has such an effect on the result. It’s impossible to get the right result, and even worse when we just look at the date by applying UTC+2 in time zones Grafana makes us think we’re smme at the right date when in fact it’s only modifying the date imported from the database but not actually fetching the data at that date.

How we can fix this plz ?

2

grafana requires your datetime fields to be utc. if not use functions that make the date time UTC. what time zone are you in? Africa/Ethiopia etc

SELECT CONVERT(DATETIME,
                @dt AT TIME ZONE 'Central European Standard Time'
                    AT TIME ZONE 'UTC');
2 Likes
3

Thank you for your answer, it’s really not practical …, ed plus I couldn’t find a way to apply this conversion to the $__timeFilter() macro, I had to use $__timeFrom() AND $__timeTo()… $__timeFilter() is only useful if you’re in UTC, otherwise you might as well do something else…

4

what happens if you crack the timeFilter macro with…

when
starttime BETWEEN $__timeFrom() AND $__timerTo()

??

1 Like
5

By selecting my time zone in the time picker, and replacing $__timeFilter(date) with Date AT TIME ZONE ‘Central European Standard Time’ BETWEEN $__timeFrom() AND $__timeTo() it works correctly. But it’s a pity you can’t use $__timeFilter easily if you’re not in UTC+0.

1 Like
6

you can just use cte to convert the data before applying that function

;with utc_converted
as
(
select id, 
CONVERT(DATETIME,
starttime  AT TIME ZONE 'Central European Standard Time' 
AT TIME ZONE 'UTC') as starttime 
from xys
where equipment = 'sdsds'
)
select top 1 id, starttime  
from utc_converted
where$__timeFilter(starttime  )
order by date desc

not for copy pasta

7

Thanks for the tip :slight_smile: , but this need to adapt the date to use $__timeFilter is a shame but at least I have a solution

8

it would be a shame for that function to try to account for all the possible time zones. thats why utc is the preferred standard everyone can agree on

9

Bom dia pessoal, sou novo no grafana, alguém poderia me ajudar em como deixa o grafana filtrando assim ?

Captura de tela 2024-09-13 122920
Captura de tela 2024-09-13 1229201478×331 41.5 KB

10

Ja tenho a query, porem não consigo fazer aparecer esse grafico ai

11

SELECT FGO_DH_IMP, count(FGO_SEQFGO)
FROM EXTGOR
WHERE FGO_DH_IMP >= DATE_SUB(now(), INTERVAL 5 HOUR)
AND CONCAT(LPAD(HOUR(FGO_DH_IMP), 2, 0), ’ ', LPAD(MINUTE(FGO_DH_IMP), 2, 0)) >= CONCAT(LPAD(HOUR(DATE_SUB(CURTIME(), INTERVAL 1 HOUR)), 2, 0), ’ ', LPAD(MINUTE(DATE_SUB(CURTIME(), INTERVAL 1 HOUR)), 2, 0))
AND CONCAT(LPAD(HOUR(FGO_DH_IMP), 2, 0), ’ ', LPAD(MINUTE(FGO_DH_IMP), 2, 0)) <= CONCAT(LPAD(HOUR(CURTIME()), 2, 0), ’ ', LPAD(MINUTE(CURTIME()), 2, 0));

13

por favor abra um novo tópico.