sys.dm_exec_connections returns local server time, not UTC about opserver HOT 4 CLOSED

Merging into #250 - plan is to solve this in a central way.

from opserver.

The trick here is that SQL server is not time-zone aware here (or pretty much anywhere). So I have to do a date difference to something. Is your web server running in the same time zone as your SQL server? If that's common then I can just compare to local date time which covers the most cases we can.

And just for my own curiosity, why not run your servers on UTC time? At Stack we can't imagine the chaos of global data with non-standardized time...so I'm very curious when I come across this.

from opserver.

The web server is in the same timezone as the SQL server. They're not my servers btw, if they were then they would be on UTC :)

The reason they're not on UTC is that years ago, someone decided they should be in the local time zone. And I can't even imagine how much stuff would break if the time zone was changed.

Perhaps a call to SYSDATETIMEOFFSET() could be cached and utilised to handle this?

from opserver.

You could convert the time to UTC with something like this (using connect_time as an example):

SELECT  connect_time,
        DATEADD(MINUTE, DATEDIFF(MINUTE, GETDATE(), GETUTCDATE()), connect_time)    AS connect_time_utc
FROM sys.dm_exec_connections

That will work on versions earlier than SQL 2008 too. Where it falls down is the fact that during a daylight savings change it will potentially report an incorrect UTC value.

from opserver.