Conversion of UTC time to LOCAL in SQL Query output

Question

I'm struggling with a SQL query - a basic select statement with a UTC time conversion in the select output. I've done a bunch of searching on this and through all of that have arrived at the following query, but it does not seem to work for me.

SELECT [Id]
      ,[BaseSource]
      ,[Source]
      ,[Output]
      ,[SessionId]
      ,[Timestamp]
      ,(SELECT DATEADD(MILLISECOND,DATEDIFF(MILLISECOND,getutcdate(),GETDATE()),[Timestamp])) as LocalTime
  FROM [MYDB].[dbo].[SystemOutput]
  ORDER BY [Timestamp] DESC

This line is where the magic should be happening, but instead i get a exact match of the Timestamp column value, so no conversion happening at all:

(SELECT DATEADD(MILLISECOND,DATEDIFF(MILLISECOND,getutcdate(),GETDATE()),[Timestamp]))

Does anyone know of how to get this working?

Thanks

Solution

First, understand that getdate() returns the local date and time of the server - not of the person running the query. If the server's time zone is set to UTC, then it will indeed return the UTC time.

Since you are running on SQL 2016, and you are asking for the UTC time converted to Pacific time, I suggest you use the built-in AT TIME ZONE statement, as follows:

SELECT [Id]
      ,[BaseSource]
      ,[Source]
      ,[Output]
      ,[SessionId]
      ,[Timestamp]
      ,[Timestamp] AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' AS LocalTime
FROM [MYDB].[dbo].[SystemOutput]
ORDER BY [Timestamp] DESC

Note, the above assumes that the [Timestamp] field is a datetime or datetime2, and that it is a UTC-based value. The first AT TIME ZONE makes an assertion that the given value is in UTC, resulting in a datetimeoffset. The second AT TIME ZONE then converts from UTC to Pacific time.

If instead the field is already a datetimeoffset type, then the query is even simpler:

SELECT [Id]
      ,[BaseSource]
      ,[Source]
      ,[Output]
      ,[SessionId]
      ,[Timestamp]
      ,[Timestamp] AT TIME ZONE 'Pacific Standard Time' AS LocalTime
FROM [MYDB].[dbo].[SystemOutput]
ORDER BY [Timestamp] DESC

Also, don't be confused by the word "Standard" in the time zone identifier. That value covers both standard time and daylight time, as applicable in the Pacific time zone in the US and Canada.

Comments

[Matt Johnson-Pint]

This isn't a good approach. It assumes you want the time zone of the SQL Server. Also, it assumes that the current offset is applicable for all values - which in many cases it is not (due to DST, for example). Can you please tell us what you are actually wanting to do (whose "local" is it?) Also, what version of SQL Server are you using?

[Nugs]

Hi there, thanks for the reply. Firstly i want to stress that this is simply for my own personal/development use to make my queries and the output of this particular table more legible re. the timestamp. Normally any conversion of UTC time is happening at our application level and i definitely would not be doing a datetime conversion within the SQL query. What i'm looking to do is get local (or PST time) from the timestamp in the SELECT output. I'm hitting our production SQL 2016 server.

[Nugs]

You the man! This is exactly what i needed. Thank you very much!

[Jeff Moden]

@Matt Johnson-Pint - I agree... you're definitely "da MAN!" on his one. I wish they'd use your explanation and examples in the MS documentation (of course, I might have missed it, as well).