Datetime picker, query parameter formats and timezones


I am a new kid on the block and have noted several issues related to time and datetime formats and which appears to be related to many queries many of which are not well explained.

I have worked extensively in metering and AMR systems and have noted such problems crop up elsewhere and which are not well explained by the documentation and which result in small detailed discrepancies. which in utility management, can create serious issues.

GRAFANA makes it easy to select the correct time zone but what it fails to note is that it and many other systems work in UTC time and not local time.

So for example in the date picker GRAFANA readily allows one to select the local time zone which allows the date picker to reflect local time wherever that maybe. However, what many appear to miss is that GRAFANA adjust or modifies any DATETIME from the picker and parses it to any of its query parameters in UTC DATETIME format rather than the format specified in the date picker. For example the figure below shows the dates in / from the Date Picker


However, when these DateTime values are parsed to any query we get


As may be seen GRAFANA automatically parses the DateTime values in UTC format and adds T and Z as shown above. The addition of Z at the end forces the system being queried to use UTC time and not local time.

This in itself is not a problem and in my case I have an account with the system being queried and hence it knows my local time zone offset. So if I send time = 02:00 GRAFANA sends 00:00 but the system being queried knows by Time Zone offset so it responds with UTC+2hrs as shown below;

response:"SUCCESS2021-03-01 00:00:00.0002021-03-23 08:07:21.303
and as may be seen time is returned correctly in local time format without any T or Z.

In metering systems time is always logged in UTC and in many instances the database carries both UTC and Local time where local time is offset by the local time zone offset Z whatever that may be.

In this case I am using a GET call to a PostGresSQL database via and xml rest interface and it took some time to fathom this out, when it should have been an obvious and well documented matter.

In many instances many IoT widgets do not store data in both UTC and LT and then try to interface them with systems (such as GRAFANA) which conform to this standard and which results in tears of frustration to many users when in fact the issue is quite simple to resolve.

This is why some IoT devices for example parse time in UTC (with z on end) which helps resolve issues for example where a meter reading data is in one time zone and the server and user is in another and this easily allows the local user to see readings as they would appear in their time zone.

So if a meter is in UTC-02 and the user is in UTC+02 then the meter local time must have 2 hrs deducted to get UTC and then it must add 2hrs to UTC to get local.

So 00:00 local at the meter becomes 02:00 UTC which becomes 04:00 local at user thereby correctly reflecting the 4 difference in timezone.

Most, if not all database systems have a convert to UTC function, but it appears the common practice is to use UTC to interface or interact between systems. If one wishes to see the same Datetime as the meter sees then one wili have to use the timezone at the meter in GRAFANA and the 2 will tie up, failing which one will always see the time difference.

Those who are diehards will find this possible trivial but many newbies should be made aware as this may create many unnecessary hardships.