Login Enterprise and UTC Timestamps in SQL

One of the downsides of querying the LE SQL database rather than using the API is that all of the timestamps are recorded in UTC, where the API converts it to you local timezone. So, after Googling a bit, I found this article on StackOverflow which details on how to do this.

The key code is:

AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time'

Note: As the article states, you can view the full list of timezone names by executing the following query:

SELECT * FROM sys.time_zone_info 

So, whereever you are pulling data from the SQL database, you can have SQL convert to your timezone for you.

Examples

As an example, if I use the built-in Select Top 1000 Rows on the LoginEnterprise.Measurements table, I see the following results.

Although it’s not evident, the values in the Timestamp column are in UTC. So, I can modify the query to convert these to Eastern US time. With this change, the query looks like this:

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP (1000) [MeasurementId]
      ,[UserSessionId]
      ,[Timestamp] AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' as Timestamp
      ,[AppExecutionId]
      ,[Duration]
      ,[ApplicationId]
  FROM [rnbsvsi-apl01].[LoginEnterprise].[Measurements]

The results now look like this:

You can see that the timestamp now reflects the Eastern US timezone.