Leave times alone, feature :D

[mixmastamyk]

Please include this information:

What Grafana version are you using? 5.1

What datasource are you using? MS SQL

What OS are you running grafana on? Ubuntu 18.04

What did you do? Had to interface with a legacy DB using local times.

What was the expected result? Correct results

What happened instead? Time off 7 hours :-D

I understand the preference for UTC everywhere, but some of us have to interface with legacy data stored in local time. Would appreciate an option, perhaps in the Datasource config to choose "local time."

Check out the gnarly SQL that has to be written to undo the time transform at the browser in the grafana app. As described in the thread at:
https://community.grafana.com/t/datasource-is-in-local-time-hilarity-ensues/7323

Should be easy to implement, simply leave the times alone? Thanks.

[yellowpattern]

Why not just create a view in the database of that table with UTC time and query the view instead of the table?

[mixmastamyk]

I don't currently have enough admin rights to create views at the db server on site, however your suggestion is intriguing @yellowpattern. Perhaps at a future date, I'll be able to use it, and this feature never gets implemented.

[yellowpattern]

talk to your dba, they get bored too and are looking to do new things so they might be willing to setup a view for you :)

[marefr]

Historically Grafana has always been expected to run o sever with UTC and using timeseries datasources running on servers with UTC. Timeseries in the beginning usually only supported UTC (epochs) as well.

I understand that there exists legacy systems out there which may cause problems, but we don't have any greater plan right now for supporting different timezones for our supported SQL datasources. If this issue gets many upvotes or someone submits a PR we'll consider this.

[mixmastamyk]

Hi @marefr, as mentioned and, unless I'm mistaken, am not asking for "timezone support."

Rather, I'm asking for an option to leave times alone, aka stop converting them when not wanted. As there is already an option for UTC/Browser time, some small steps in that direction have already been taken.

(This came about due to a data source in MS server, but do not believe it is relevant to the issue.)

[marefr]

UTC/Browser time is a client side conversion. Grafana expects all date/time related data coming from datasources to be in UTC timezone since the time format is epoch (unix time) which don't have any timezone info - it's always in UTC. That's why we "can't leave times alone" as you put it.

I guess this is a duplicate of #558 then if it's not mssql specific so will close this. Consider reopen if I misunderstood something.

[mixmastamyk]

Hmm, that's not right:

UTC/Browser time is a client side conversion

You are modifying the times at the browser. The timestamps from the database are in local time, despite definitions. You could leave them alone, if you chose to.

The folks in the other bug are asking for full timezone support, a huge undertaking. This bug in comparison is asking for something about 1% of the effort.

[marefr]

Let's take your example of using MSSQL with date values in local time (no timezone information). The go library Grafana are using to connect to MSSQL are returning all dates from the database as UTC, see open issue denisenkom/go-mssqldb#225. If there's timezone information of the dates read from database that will be taken into consideration and be converted to UTC before the results are returned to Grafana client side where the UTC/Browser time setting are applied to show the date/time in certain timezone.

Hope this makes it more clear.

[mixmastamyk]

Thanks for bug link.

So, grafana could leave times alone, if the library it uses were enhanced. There is even an incomplete pull request. It would need matching work done here, so it might make sense to leave this bug open. Now that I think of it, the grafana work is not dependent on MS-SQL, would this work with other DBs?

[marefr]

Well in theory yes. But right now Grafana (xorm abstraction over relational databases) are configured to read all dates from database in UTC that's not having a column/datatype which includes timezone information. So to answer your question if it would work with other DBs - the answer is currently no.

Switch to store dates in unix time (epoch) or switch to a column/data type supporting timezone information. Or use a view as suggested above.

[mixmastamyk]

Unfortunately, I don't have permission to change the database, it is critical to the business. :-/

[marefr]

If it's critical to the business I doubt that you want to integrate Grafana with that database - would suggest some sort of ETL flow to populate a different and separate database. Very common use case in BI solutions.

[zbdbx]

I think it's usually use local time if it's not an internationalized application in sqlserver.
It's a tedious thing to use the ETL tool,because there are probably many more columns such as CreateTime,UpdateTime,AssginTime use local time. And It's not real time data to show.
Do you have an alternative solution to the problem?