Transact SQL – Time Difference between System Time and UTC

Background

SQL Server is starting to use UTC/GMT time more and more.

And, it is a good thing.

 

Where is GMT/UTC Used?

For instance GMT /UTC is the only time used in Extended Events.

 

Sample XML Payload

Image

Explanation

The time the event was raise is available :-

  1. timestamp
  2. collect_system_time

 

Convert UTC Time to System Time

Get Number of Minutes between GMT/UTC Time and our system’s time

Outline

  1. Get current date & time
  2. Get UTC Date & time
  3. Use datediff and get difference in minutes

Function – Scaler

dbo.udfn_getTimeDifferenceBetweenUTCAndSystemTimeInMinutes


use [master]
go

/*
	drop function [dbo].[fn_getTimeDifferenceBetweenUTCAndSystemTimeInMinutes]
*/
if object_id('[dbo].[udfn_getTimeDifferenceBetweenUTCAndSystemTimeInMinutes]') is null
begin

	exec('create FUNCTION [dbo].[udfn_getTimeDifferenceBetweenUTCAndSystemTimeInMinutes]()
			returns smallint 
			as 
			begin  
				return 1/0
			end
		')

end
go
ALTER FUNCTION [dbo].[udfn_getTimeDifferenceBetweenUTCAndSystemTimeInMinutes]
(
)
RETURNS smallint
AS
BEGIN

	declare @iTimeDifference   int
	declare @datetimeNowSystem datetime
	declare @datetimeNowUTC	   datetime


	set @datetimeNowSystem = getdate()
	set @datetimeNowUTC = GETUTCDATE()

	set @iTimeDifference
			= datediff
				(
					  minute
					, @datetimeNowSystem
					, @datetimeNowUTC
				)

	return ( @iTimeDifference )


    RETURN (@iTimeDifference);
END
go

grant exec on [dbo].[udfn_getTimeDifferenceBetweenUTCAndSystemTimeInMinutes] to [public]
go


Review Extended Events

Code


declare @extendedEvent sysname

declare @OSDrive		varchar(80)
declare @folderBase		varchar(200)
declare @folder			varchar(200)
declare @filename		varchar(200)

declare @filenameXEL	varchar(200)
declare @filenameXEM	varchar(200)


set @extendedEvent = 'performance'

set @OSDrive = 'E:'
set @OSDrive = 'L:'

--set @folderBase = '\Microsoft\SQLServer\ExtendedEvent\Events\'
set @folderBase = '\Microsoft\SQLServer\ExtendedEvents\TraceFiles'

set @folder = @OSDrive + @folderBase + '\' 
set @filename = @folder + @extendedEvent 


set @filenameXEL = @filename + '*.xel'
set @filenameXEM = @filename + '*xem'

 
; with cte
(
      [objectName] 
    , [eventData] 
)
as
(
    SELECT top 1000
 
          [objectName] = tblFNXE.[object_name]
        , [eventData] = cast(tblFNXE.event_data as xml)
 
    FROM sys.fn_xe_file_target_read_file
        (
              @filenameXEL
            , @filenameXEM
            , null
            , null
       ) tblFNXE
)
select
 
          [objectName] = cte.[objectName]
 
        , [eventData] = cast(cte.eventData as xml)
        , [XMLFragment] = [event].[node].query('.')
 
        --timestamp on root node
        , [tsAsDateTimeUTC] = [event].[node].value('@timestamp', 'datetimeoffset')     
 
        --collect system time
        , [systemTSAsValueUTC] = ([event].[node].query('/event/action[@name="collect_system_time"]/value/text()'))
        --, [systemTSAsTextUTC] = ([event].[node].query('/event/action[@name="collect_system_time"]/text/text()'))

        , [systemTSAsValueSystem] 
		= 
			convert
				(  
					  varchar(30)
					, dateadd
					(
					     minute
					   , [dbo].[udfn_getTimeDifferenceBetweenUTCAndSystemTimeInMinutes]() * -1
 					   , cast
					     (
						   cast
						   (
							  ([event].[node].query('/event/action[@name="collect_system_time"]/value/text()')) 
							   as varchar(60)
						   )
						   as datetime
					    )
					)
					, 100
				)
				
        --session ID
        , [sessionID] = [event].[node].query('/event/action[@name="session_id"]/value/text()')
 
        -- client Host Name
        , [clientHostName] = [event].[node].query('/event/action[@name="client_hostname"]/value/text()')
 
        -- username
        , [username] = [event].[node].query('/event/action[@name="username"]/value/text()')
 
        -- client App Name
        , [clientAppName] = [event].[node].query('/event/action[@name="client_app_name"]/value/text()')
 
        -- SQL Text
        , [sqlText] = [event].[node].query('/event/action[@name="sql_text"]/value/text()')
 
from   cte
 
cross apply cte.eventData.nodes(N'//event') event(node)


 

Output

 

Explanation

  1. GTM/UTC Time
    • tsAsDateTimeUTC
      • 2017-04-11 12:00:01.9150000 +00:00
    • systemTSAsValueUTC
      • 2017-04-11T12:00:01.916Z
  2. System Time
    • systemTSAsValueSystem
      • Apr 11 2017  5:00AM

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s