$__timeFilter() no longer respects the local time zone

[bontchev]

What Grafana version are you using?

Grafana v5.3.1 (08c7908)

What datasource are you using?

MySQL

What OS are you running grafana on?

Ubuntu Linux 16.04

What did you do?

I didn't really do anything. I just noticed that one of the panels on my dashboard is sort of broken - it is missing the last 2 hours of data. It was working perfectly in the previous version of Grafana (5.2.x).

The panel is a simple graph, displaying an hourly bar histogram of the data collected during the corresponding hour. So, why were the last two hours missing? I hadn't changed anything on the panel settings or in the MySQL query that feeds it the data.

Examining the generated MySQL query showed that the macro $__timeFilter(timestamp) had expanded to

timestamp BETWEEN '2018-10-18T20:19:53Z' AND '2018-10-19T20:19:53Z'

Here timestamp is a column in a table of the MySQL database with the format datetime. The above query was generated at 22:19 local time. My time zone is UTC+2.

Do you see what is happening? The "from" and "to" times passed to the query are in UTC time and not in local time - despite the dashboard being configured to use local time. As a result, the query fetches data for the wrong time period.

Now, I did find a workaround for the problem. Instead of using the simple $__timeFilter(timestamp) macro, I have to use

UNIX_TIMESTAMP(timestamp) BETWEEN $__unixEpochFrom() AND $__unixEpochTo()

This works with UTC times only and the data is fetched as it should be. It would have been more intuitive to use just $__unixEpochFilter(UNIX_TIMESTAMP(timestamp)) but, due to the way macros are implemented, you can't pass them the result of a function instead of a simple column name. (I had opened an issue on the subject, but you closed it and didn't fix that problem, although the fix is rather trivial - I managed to figure it out despite that I don't know Go.)

Would it be possible to restore the old behavior of the $__timeFilter() macro? In fact, the problem probably isn't in the macro itself, but in the format of the times passed to it - they should be in local time, if the dashboard is configured to use the local time of the browser.

[bontchev]

OK, turns out that using

UNIX_TIMESTAMP(timestamp) BETWEEN $__unixEpochFrom() AND $__unixEpochTo()

instead of $__timeFilter(timestamp) isn't really practical because it is too slow. The table is huge and is indexed by timestamp, not by UNIX_TIMESTAMP(timestamp).

For the record, the macros $__timeFrom() and $__timeTo() suffer from exactly the same problem - they return the time in UTC instead of in the local time zone of the browser. This really screws up my panel and I don't know how to fix it. Things used to work just fine!

Here is how my panel looks like; the screenshot was taken at 13:53 local time. Notice how the last bar of data is from 10:00, instead of from 13:00:

For completeness, here is the Grafana query I use for this panel:

which translates to the following MySQL query after the resolution of the macros:

SELECT
  UNIX_TIMESTAMP(timestamp) DIV 3600 * 3600 AS "time",
  COUNT(success) AS value,
  'Logins' AS metric
FROM auth
WHERE
  timestamp BETWEEN '2018-10-20T10:53:25Z' AND '2018-10-21T10:53:25Z'
GROUP BY DATE_FORMAT(timestamp, '%Y-%m-%d %H')
ORDER BY timestamp ASC

Any ideas how to fix this? Or some kind of workaround? I can't use CONVERT_TZ() to adjust the results returned by $__timeFrom() and $__timeTo(), because I don't know what the time zone of the viewer will be. (Wish there were a macro returning that information.)

[bontchev]

Maybe the whole problem occurs because MySQL does not understand the 'Z' date format specification as specifying UTC time? See this.

For instance,

SELECT
  TIME('2018-10-20T18:33:36Z') AS UTC,
  TIME('2018-10-21 18:33:36') AS Local

results in the same times being displayed:

UTC	Local
00:20:18	18:33:36

which is pretty meaningless...

[torkelo]

Grafana SQL data sources only support date column data stored UTC. It was by accident that
UNIX_TIMESTAMP(timestamp) BETWEEN $__unixEpochFrom() AND $__unixEpochTo() worked with local dates. We had to change that as it was not performant.

results in the same times being displayed:

They look totally different (the ones you printed). Not sure what you mean.

[bontchev]

@torkelo, you have the horribly annoying habit of closing issues when they are still not resolved! This is simply rude! I am tempted to start re-creating the issue when you close it until it is resolved properly. Please, stop doing this!

As it is now, the macros $__timeFilter(), $__timeFrom(), and $__timeTo() are simply do not work with a MySQL data source unless the viewer resides in the UTC time zone.

I did find a work-around that is fast enough, because it doesn't require applying a function to timestamp and the database is indexed by simply timestamp. The workaround looks like this:

timestamp BETWEEN FROM_UNIXTIME($__unixEpochFrom()) AND FROM_UNIXTIME($__unixEpochTo())

but it is ridiculous what I have to resort to such hacks just because the documented macros do not work properly! Please fix them!

Regarding the time - it's a typo; a leftover from a previous redaction. Yes, they are totally different, instead of being the same. This suggests that MySQL simply does not understand the UTC timestamp notation.

[torkelo]

Sorry I must have misunderstood the issue or you have misunderstood something. The viewer does not need to be in UTC but the date stored needs to be stored in UTC.

Is your data stored in UTC ?

[torkelo]

This suggests that MySQL simply does not understand the UTC timestamp notation.

It suggest the opposite, if they where same then you would be correct. As they are different it seems to work correctly (being translated as UTC) the reason they are different suggest you are running your MySQL server in a non UTC time environment/ setting.

[timmmmmeh]

Maybe the whole problem occurs because MySQL does not understand the 'Z' date format specification as specifying UTC time? See this.

For instance,

SELECT
  TIME('2018-10-20T18:33:36Z') AS UTC,
  TIME('2018-10-21 18:33:36') AS Local

results in the same times being displayed:

UTC Local
00:20:18 18:33:36
which is pretty meaningless...

Yup, this seems to be the issue for us as well. Our servers are in Los Angeles and their timezones are configured as such (PDT, GMT-7). Our dates are stored as timestamps, so timezone isn't relevant. The format Grafana is sending the date to MySQL (UTC with the 'Z' designation) doesn't seem to be understood by MySQL. These timestamps should, in theory, be the same:

mysql> select 
    unix_timestamp('2018-10-22T13:00:00') as PDT, 
    unix_timestamp('2018-10-22T20:00:00Z') as UTC from dual;  
+------------+------------+  
| PDT        | UTC        |  
+------------+------------+  
| 1540238400 | 1540263600 |  
+------------+------------+  

[svenklemm]

If you are storing timestamps in local time then your schema does not handle timezones properly and your data becomes ambiguous if your server is in a timezone with daylight saving time.

[timmmmmeh]

In our case we store data as timestamps. Timestamps contain no timezone info. The server timezone affects how the timestamp is formatted when displayed in a human-readable format, but it does not affect the underlying storage. The server is in Los Angeles and uses the local timezone (PDT, UTC-7):

mysql> create table test_data (date timestamp);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test_data values (now());
Query OK, 1 row affected (0.01 sec)

mysql> select * from test_data;
+---------------------+
| date                |
+---------------------+
| 2018-10-23 09:01:39 |
+---------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp(date) from test_data;
+----------------------+
| unix_timestamp(date) |
+----------------------+
|           1540310499 |
+----------------------+
1 row in set (0.00 sec)

mysql> set time_zone='UTC';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_data;
+---------------------+
| date                |
+---------------------+
| 2018-10-23 16:01:39 |
+---------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp(date) from test_data;
+----------------------+
| unix_timestamp(date) |
+----------------------+
|           1540310499 |
+----------------------+
1 row in set (0.00 sec)

Note that the underlying epoch timestamp is constant. The only change is in the formatting of the human-readable date. The problem is that when Grafana sends a date to MySQL (e.g. '2018-10-20T10:53:25Z'), MySQL doesn't respect that 'Z' UTC designation. The date is being parsed in the server's configured timezone.

One solution that might be easier than trying to fiddle with MySQL timezone parsing would be to issue a
set time_zone='UTC'; statement upon establishing the connection to the database. That will set the connection's timezone for the rest of the session. One caveat is a database administrator needs to make sure the timezone data is loaded (see here). So maybe a checkbox when setting up MySQL as a data source for whether or not to issue this set time_zone command on establishing the connection would work.

[superbool]

I got the same problem.so I diff the code before v5.3.0 such as v5.2.4 mysql "__timeFilter" format like this:

grafana/pkg/tsdb/mysql/macros.go

Line 80 in 0bbac5c

return fmt.Sprintf("%s >= FROM_UNIXTIME(%d) AND %s <= FROM_UNIXTIME(%d)", args[0], m.TimeRange.GetFromAsSecondsEpoch(), args[0], m.TimeRange.GetToAsSecondsEpoch()), nil

Then at master:

grafana/pkg/tsdb/mysql/macros.go

Line 63 in 6932306

return fmt.Sprintf("%s BETWEEN '%s' AND '%s'", args[0], m.timeRange.GetFromAsTimeUTC().Format(time.RFC3339), m.timeRange.GetToAsTimeUTC().Format(time.RFC3339)), nil

so if we query with params(UTC+8 Shanghai,China):

from:"1540274787237"
to:"1540361187237"

before format result:

 xxx>= FROM_UNIXTIME(1540274787) AND xxx<= FROM_UNIXTIME(1540361187)

but the new result:

xxx BETWEEN '2018-10-23T06:06:27Z' AND '2018-10-24T06:06:27Z'

let's query from mysql :

select   FROM_UNIXTIME(1540274787) as a,FROM_UNIXTIME(1540361187) as b

that is different time rang, the function GetToAsTimeUTC did't work with time zone.

[superbool]

here is my test case:

[superbool]

I have replace all 'GetFromAsTimeUTC' with

func (tr *TimeRange) GetFromAsTimeLocal() time.Time {
	return tr.MustGetFrom().Local()
}

and 'GetToAsTimeUTC' with

func (tr *TimeRange) GetToAsTimeLocal() time.Time {
	return tr.MustGetTo().Local()
}

in tsdb.time_range.go. It works fine for me.

[marefr]

@ArmedChef what do you get in your testcases if you use SELECT FROM_UNIXTIME(1540310499)? Based on the documentation it should be timezone aware so we may be able to use FROM_UNIXTIME on the from/to timestamp we use to compare against column in $__timeFilter. With this we should still get an optimized query plan if timestamp column in database are indexed.

[timmmmmeh]

@marefr yes, it is respecting the configured timezone (just a reminder mine is PDT/UTC-7). I think that solution should work.

mysql> SELECT FROM_UNIXTIME(1540310499);
+---------------------------+
| FROM_UNIXTIME(1540310499) |
+---------------------------+
| 2018-10-23 09:01:39       |
+---------------------------+
1 row in set (0.00 sec)

mysql> set time_zone='UTC';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT FROM_UNIXTIME(1540310499);
+---------------------------+
| FROM_UNIXTIME(1540310499) |
+---------------------------+
| 2018-10-23 16:01:39       |
+---------------------------+
1 row in set (0.00 sec)

[marefr]

Thanks @ArmedChef looking promising!

[timmmmmeh]

@marefr Thanks for looking into this. Just a follow up since I forgot about indexing. This looks like it will work with indexing on timestamp columns.

mysql> explain select * from test_data where date between from_unixtime(1540300499) and from_unixtime(1540400499);
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test_data | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> create index  date_idx on test_data(date);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from test_data where date between from_unixtime(1540300499) and from_unixtime(1540400499);
+----+-------------+-----------+-------+---------------+----------+---------+------+------+--------------------------+
| id | select_type | table     | type  | possible_keys | key      | key_len | ref  | rows | Extra                    |
+----+-------------+-----------+-------+---------------+----------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test_data | index | date_idx      | date_idx | 4       | NULL |    1 | Using where; Using index |
+----+-------------+-----------+-------+---------------+----------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

[torkelo]

Thanks for doing that test , we will change this and release a patch next week. Sorry for all the trouble this caused.

[bontchev]

Sorry, folks, I was away for a while and couldn't follow the discussion. Let's see if I could contribute anything useful.

1. MySQL always stores datetime values in UTC internally. It automatically converts them to the time zone of the viewer. The latter is configurable, but this is useless in this case, because the time zone of the MySQL server is not necessarily the same as the one of the Grafana user.

2. @torkelo, what I meant was that MySQL does not understand timestamp notation like '2018-10-20T18:33:36Z'. Check the MySQL documentation for STR_DATE(), for instance, and you'll see that it has no equivalent for specifying that the time zone is UTC by appending Z to the time. So, my conjecture is that it doesn't realize that you're giving it UTC time, ignores the characters it doesn't recognize, and converts the result from the assumed local time (which is wrong, because you've given it UTC time) to UTC time internally, resulting in wrong timestamp.

3. The way to solve this efficiently is pretty much to do what I've done in my workaround. Do not touch timestamp (i.e., do not apply UNIX_TIMESTAMP() to it), because the table might be indexed by timestamp but not necessarily by UNIX_TIMESTAMP(timestamp) and this will slow things down. Instead, apply FROM_UNIXTIME() to the time boundaries submited as Unix epoch timestamps. This will be fast and will be executed only once, after which MySQL will just be comparing to constants.

4. This issue is MySQL-specific. I am not using other kinds of data sources, but my guess is that they do understand the Z notation to mean that the timestamp is in UTC, so the problem doesn't occur and you haven't noticed it before.

[marefr]

@bontchev welcome back. This was fixed 5 days ago using 3) and will be released in upcoming v5.3.3 release.

[yangliuyu]

@bontchev I tested on v5.3.4, seems still not fixed correctly.
###What Grafana version are you using?
Grafana v5.3.4 (69630b9)

###What datasource are you using?
MySQL 5.7

What OS are you running grafana on?
CentOS 7.4

What did you do?
My browser, grafana-server and mysql server are all in timezone GMT/UTC+8, I query in my local browser with query time "Today"

and grafana will translate the time range to

select from_unixtime(1542211200); -> 2018-11-14 16:00:00

My order_created_at field is timestamp type and stored in mysql with UTC time internally.

The problem I think is grafana add extra UTC time conversion from ui date picker to query string.
What I am expect is using exactly the same as date picker time in my query without any utc conversion.

[marefr]

@yangliuyu what Grafana version did you use before where this worked for you?

select from_unixtime(1542211200); -> 2018-11-14 16:00:00

This tells me that your timezone in mysql session is utc.

[bontchev]

@yangliuyu, my Grafana just updated to version v5.3.4 (69630b9) - missed 5.3.3, for some reason - and, as far as I can see, the issue is resolved there. $__timeFilter(timestamp) now works correctly and expands to my local time the way I suggested (timestamp BETWEEN FROM_UNIXTIME(1542192333) AND FROM_UNIXTIME(1542278733)).

MySQL always stores datetime values in UTC; check if your Grafana dashboard is configured to use the browser local time or not, what is the time zone of the MySQL session, etc.

[yangliuyu]

@marefr You are right, I rechecked and it works now, I tested using a docker mysql 5.7 with utc timezone yesterday, and my timezone in production environment of mysql is UTC+8.

[devsschmidt]

doesnt work for PostgreSQL either. or has anyone a Workaround? for some tables i cant Change the data and the timestamps are simply not in UTC

[devsschmidt]

I've fixed the Situation by adding '::TIMESTAMP WITH TIME ZONE AT TIME ZONE 'UTC'' to the SQL Statement

e.g.

SELECT
  my_timestamp_column::TIMESTAMP WITH TIME ZONE AT TIME ZONE 'UTC' AS "time",
  my_metric_column
FROM my_table
WHERE
  $__timeFilter(my_timestamp_column::TIMESTAMP WITH TIME ZONE AT TIME ZONE 'UTC')
ORDER BY 1

[marefr]

@devsschmidt what postgres version? What data type have my_timestamp_column?

[PimpJuiceIT]

I'm glad I read the release notes—I must be chopped liver.... #13694. I wish I would have read about that Macro update Git dev version thing back in October though.

I came up with a MySQL Event Scheduler and Grafana Variable workaround where each DB has it's own timezone time value records and timezone is of no importance as it adjusts for DST already too. The Grafana SQL I used was WHERE TimeStr BETWEEN $__timeFrom() + INTERVAL $TimeZone HOUR AND $__timeTo() + INTERVAL $TimeZone HOUR

https://i.imgur.com/ktEsgRD.png

It sounds like the old WHERE $__timeFilter(TimeStr) solution will now work in Grafana again—Great!!!

@torkelo @marefr

[paolss]

same problem for psql

[shavetasidana]

$__to() and __from() variables return the time in UTC instead of local time zone of the browser. I am using Grafana 6.0.2 . I need time in local timezone. Adding a GMT difference solves the issue, but the viewers can be in different timezones. Can someone suggest a fix?

Additional Information: Grafana Dashboard is set to use "Local Browser Time".

[marefr]

@shavetasidana that's intentional since the format is epoch milliseconds and epoch is always UTC.

[shavetasidana]

ok.
My problem is :
I have to display __from and __to values on my dashboard.

For that I have used the sql Query, " select from_unixtime($__from/1000,"%Y-%m-%d") as "Start Date", from_unixtime($__to/1000,"%Y-%m-%d") as "End Date" from DUAL"

The date should be displayed as it is selected from the time range Panel, but actually I have to add GMT difference. Otherwise, it displays previous date as start date.

Preference setting of Timezone to "Local Browser Time" or "UTC" does not make any difference.

Can you give an idea how to solve this issue? How do I know local Time Zone (for adding GMT difference to the time returned by __from variable?). Does grafana provide timezone information in someway?

[shavetasidana]

@marefr, could you help me please on my above query? Thanks in advance!!

[marefr]

@shavetasidana $__to and $__from is always epoch ms and in UTC timezone (nature of epoch). The only way you can solve this is to timeshift the time in your query (Grafana can't help or support this use case).

[shavetasidana]

Thanks for your answer marefr, I will check how to do the timeshift.

[AlexHeylin]

For what it's worth - an easy workaround would be to issue

set time_zone='+00:00'; 

as the first line of the query. Although this works perfectly when done natively, trying to do this via Grafana results in

Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT
  UNIX_TIMESTAMP(timest) DIV 86400 * 86400 as time,
  concat(' ',max(date' at line 3

I've tested this works natively by taking the generated SQL (with set line at the front) and run the code in HeidiSQL. I get the expected output, with timestamps in UTC. I assume Grafana is trying to do something helpful with the set line and screwing up the query when it runs it - because it can't be running the code that the Generated SQL shows it to be running as I've proved that code works fine.

A much more durable solution would be for Grafana to explicitly set its MySQL session to "+00:00" when connecting. AIUI this would entirely resolve this issue across the board, without any special handling by the user. This was already suggested in #15321

I suggest we need to either allow

set time_zone='+00:00'; 

to work properly, or implement this natively on connect by default (which would be much better)?

It's already been suggested in #18120 to make the connect timezone configurable but default to "+00:00" which makes perfect sense to me as it handles default use case and allows special use cases too.

[vbiftr]

I think the MSSQL connection plugin have the same issue. MSSQL does not unerstands the Z time notation by simply ignoring the Z at the end of the time string.

[971jmd]

bonjour Hello

J'ai un problème:

SELECT
Date_Heure_Alarme AS "time",

count(Def_Balance)
FROM alarme_trt

WHERE
$__timeFilter(Date_Heure_Alarme)
ORDER BY Date_Heure_Alarme

Jusque-là ça fonctionne
until then it works

Je souhaite afficher le résultat selon ma sélection date et heure dans le dashboard

I want to display the result according to my date and time selection in the dashboard

[errolt]

This is SUCH a mess.

We use MSSQL, and we store LOCAL time in a datetime column, which does NOT store timezone information.

Graphana now dictates that we change what we store to be compatible with graphana's insistence that all dates be stored in UCT?

[AlexHeylin]

@errolt I don't think Grafana insists they're stored in UTC just that it will only deal with them in UTC because it expects epoch ms and epoch is UTC.

If that's correct then could you write and use a function to convert the datetime to UTC, or directly to epoch ms in UTC. So instead of UNIX_TIMESTAMP(timest) (mysql notation) you'd use MY_UNIX_TIMESTAMP(timest) or whatever you named it.

I'm not familiar with MSSQL, but I think this would work in MySQL so probably in MSSQL too. However if you're storing in local with a DST variance then you're going to have a lot of "fun" trying to deal with that.

Look on the bright side - my largest data source stores events with datetimes without timezone, in whatever time zone they were generated in (anywhere in the world) and in a separate table holds the UTC offset that applies the the source right now (but not what applied when the data was written, and thus the offset of each event datetime). DST causes a whole bunch of fun with this, for many weeks each year the UTC datetimes cannot be correctly calculated or even assumed.

[PimpJuiceIT]

It seems the Time Series visualization $__time() and $__timeFilter() macros work differently than it does with the Graph visualization and displaying the data.

In my case:

• the Grafana server is EST/EDT always
• the MySQL server we store the data is EST/EDT always
• the data we are collecting is always in its local date time which could be EST/EDT or any other time zone
  • the collection device as the plants/facilities collect this way
• I use the TimeSeries visualization rather than Graph now

When the Grafana dash on a web browser needs to access data in a different time zone than EST/EDT, I do a few things so it displays in the right time zone on the EST/EDT computer or a CST/CDT computer either/both. I will give you an example of data that is collected on the CST time zone that allows me to see if on a CST or EST computer from its web browser.

In Grafana I…

1. set the Time options within the Dash settings from the upper gear icon to America/Chicago (I used to not do this with Graph)
2. use Grafana separate MySQL queries for each data point/tag separately with specific MySQL logic using the Grafana Macros $__time() and $__timeFilter() (I did used to do this with Graph too)
3. never have to adjust anything for DST in this configuration, but it may be due to CST and DST changing the time at the same increment (same when I used Graph here)

Query A

SELECT $__time(TimeStr + INTERVAL 1 HOUR)
                                , value
                                , 'Power' as metric
FROM Power
WHERE $__timeFilter(TimeStr + INTERVAL 1 HOUR)

Query B

SELECT $__time(TimeStr + INTERVAL 1 HOUR)
                                , value
                                , 'Temp' as metric
FROM Temp
WHERE $__timeFilter(TimeStr + INTERVAL 1 HOUR)

Query Note: With the old Graph, I used to NOT have to + INTERVAL 1 HOUR in either of the Macros and it would just work with the column name.

Other notes

I’ve yet to set any Grafana dashes up in this configuration with a time zone other than CST or EST at the moment, so I suspect the interval number in the Grafana SQL queries may need to be adjusted differently here based on time zone difference (TimeStr + INTERVAL 1 HOUR) where 1 may need to be 2 or a different number or “+” may need to be “–“ to increment it downward.

All you have to do to test is adjust the Grafana MySQL query to changes those variables mentioned above, and then change the time zones on your PC and refresh the Grafana dash to see how it looks. I like looking at my data in the server time zone I am on from a web browser, and also on the time zone the timestamp data points are on locally to confirm people in that time zone looking at it sees it right.

I’m supposed to be working with some data from another country here soon from Europe too so I’ll be digging in to understand this better myself soon.

Also, I use MySQL in my environment but those two equivalents of adding or subtracting an hour from a DATETIME column in MS SQL should apply in your case. This should be a trivial thing to figure out and test for what you will plug into the Grafana Macros.

Also in my case, I get the data from the devices that collect with their local time zone time stamp like the example below semicolon separated, but before I put that second value DD/MM/YYYY hh:mm:ss into the MySQL data table which Grafana data source points for a tag, I transform it to fit the format which MySQL DateTime requires. So in my case, I transform the data for that column which is the TimeStr in the above examples to fit into MySQL format. This should all be possible with MS SQL as well so just play with a small sample and try to work it out.

Sample Data (before I transform and put into MySQL tables)

 timeint, timstr, x, value,code
1642652940;"20/01/2022 04:29:00";0;97.22528;3

[jetic83]

I do have similar problems with Grafanas time management.

I live in CEST (= UTC+2), timezone for Berlin/Germany. We use Grafana 11.1.4 Windows, local installation (no cloud).

Default and browser time are CEST.

In my Grafana query, I use:
WHERE $__timeFilter(ScanDate)

I visualize the data from "today so far" (e.g., now, it is 18:50 CEST).

Interestingly, when using the CEST time in Grafana time picker, the above clause is translated to:
WHERE ScanDate BETWEEN '2024-08-15T22:00:00Z' AND '2024-08-16T16:48:50Z'

as shown in the query inspector. But when using UTC in Grafana, the same clause is translated to:
WHERE ScanDate BETWEEN '2024-08-16T00:00:00Z' AND '2024-08-16T16:49:30Z'

SO A SHORTENING OF 2 HOURS (I would expect a shift, but not a shortening?). Something is odd here. Today so far logically means almost 19 hours. The above query gives me that, but shifted, the below query gives me only 17 hours.

Therefore, I conclude, the template variables / shortcuts are buggy. While $__timeFrom() nicely adjusts with the timezone, I select, $__timeTo() remains always the same value. I think, this is one of the errors.