Convert SQL Dates to UTC

[ChristopherHaws]

Hello,

Most of our servers are not in UTC which causes all of the dates and times to be incorrect throughout the app. I believe that I have patched all of the places where DateTimes are read from SQL.

SQL monitors that contained DateTimes that were patched:

• Active Operations
• CPU History
• Connections
• Databases
• Database Tables
• Database Views
• Stored Procedures
• Database Backups
• Restore History
• SQL Error Log
• Job Summary
• Top Operations
• Wait Statistics

The SQL Instance Property class now includes TimeZone which falls back to UTC if it cannot read the TimeZone from the SQL Server. This property is then used by all of the above monitors to convert the DateTime's to UTC when they are being read, so the times are all stored in UTC in the caches.

Unsolvable Issue:

• Since SQL Server stores all of its DateTimes in the local system's timezone without offsets, it is not possible to determine the correct time for the two hours when switching between daylight savings time and standard time. Since I am using .NET to do the timezone conversions, all times before and after the DST switch will be accurate. Unfortunately, the only solution to this is to have the system use a timezone without DST (such as UTC) which isn't always possible, therefore SQL Server is throwing away the precision necessary to do the conversion.

I also hope you don't mind, I added a .editorconfig file. In most of my projects as well as my companies projects we use tabs instead of spaces, so I use the EditorConfig extention when working on open source projects to make sure that I stick to their standards. If you don't already know, EditorConfig is a file, that many IDE's have plugins for, that allows you to share settings such as spaces/tabs, line feed character, etc with other users.

PS: Thanks for this awesome product! :)

[NickCraver]

Thanks for the PR! Unfortunately, this is far from a simple issue and has come up before...I just haven't gotten to it in far too long (see #42). In this case, there are 2 major issues I see:

• sys.xp_regread is not documented, we can't be using that to determine offsets in SQL Server (there are other ways). There's also a permissions issue there.
• This approach ties 2 async pathways together in a synchronous way, which will result in null refs from ServerProperties.Data.TimeZoneInfo on initial fetches or any downstream fetch failures in ServerProperties.

Some example approaches (which would need integrating directly into existing queries):

• GETDATE() vs GETUTCDATE(): SQL 2005+
• SYSDATETIMEOFFSET(): SQL 2008+
• AT TIME ZONE: SQL 2016+

I just need to fire up an instance that's not on UTC time to test these a bit and have never found the time to prioritize it :(

[ChristopherHaws]

Thanks for the quick response!

I completely hear you on using an undocumented stored procedure, and I didn't want to use it. As far as I could see, there aren't too many good alternatives. As for the permission issue, I did take that into account. If you don't have permission to that stored procedure or it doesn't exist, the code will fall back and use UTC.

Here are a few approaches that I thought of, but ultimately decided not to go with:

• Query the SQL Server via WMI to get the timezone key. I don't like this because it requires that you have WMI configured and a user on the server with permissions to query via WMI.
• Using GETDATE(), GETUTCDATE() or SYSDATETIMEOFFSET() to get an offset. This approach would lead to issues when switching between DST and SDT because we would have to assume that all of the dates in the set are using the same offset. From what I can see, there is no way to determine the timezone based on the offset (for example, I am in California which is -8 right now, but there are 2 other timezone's also in -8 right now).
• Using the new AT TIME ZONE feature in 2016 wouldn't help because it still requires that you pass in a system timezone key.
• Add a config entry to the SQLSettings.json where you can manually specify the timezone the server is in. This approach could end up out of sync since it is manually set, but how often are we changing SQL Server timezones ;)

What are your thoughts on adding a setting in the SQLSettings.json file instead of using ServerProperties until we can come up with a better approach? This approach would get rid of the async issue you pointed out as well as the issue of using an undocumented stored procedure.

The settings json file would look something like this, and if the timezone is not specified then the app can assume that the server is in UTC.

{
    "defaultConnectionString": "Data Source=$ServerName$;Initial Catalog=master;Integrated Security=SSPI;",
    "instances": [
        {
            "name": "SQLSERVERNAME",
            "timeZone": "Pacific Standard Time"
        }
    ]
}

I am more that happy to go in this direction instead. It wouldn't be that big of a change from what I have already.

Let me know what you think. :)

Thanks!

[NickCraver]

Closing this out for the reasons above - but I have some ideas about how to fix this, see #250 for tracking. TL;DR: I think we can get the server timezone offset once (cached) and apply this on the query side globally in Opserver in some nice ways. I appreciate the work here, I really do - just need to approach it in a more scalable/simple way.

[danilocbraga]

Probably it's not best approach, but the only thing that I did to fix it was changing all "DateTime.UtcNow" to "DateTime.Now" in the project. Everything that was not matching before works fine now. Thx guys!