Timezone Name Conversion Between SQL Server and Javascript

Question

I have some data that I want to report on (simplified for brevity):

MyDateTime (UTC)     MyValue
------               -------
2020-10-01 10:00:00  24
2020-10-01 15:00:00  53
2020-10-02 12:00:00  26
etc

All dates are stored as UTC. Normally, I would keep everything in UTC for as long as possible, and convert to the local time in the users browser based on the users locale.

However for this particular report, I want to aggregate the data by whole dates:

SELECT CAST(MyDateTime AS DATE), AVG(MyValue)
FROM MyTable
GROUP BY CAST(MyDateTime AS DATE)

So I need to convert the date to the user's local time before I truncate the MyDateTime column and aggregate it.

In SQL Server, I can convert the UTC datetime to a local truncated date like this:

CAST(CONVERT(datetime2, (SeizeTime AT TIME ZONE 'GMT Standard Time'), 1) AS DATE)

SQL Server has a list of timezone names that the AT TIME ZONE clause accepts, in my example I'm using GMT Standard Time which is UK time including daylight savings.

Javascript running in a browser has another way of naming timezones:

console.log(Intl.DateTimeFormat().resolvedOptions().timeZone)
--> Europe/London

So my problem is that there is no common way of naming timezones between SQL Server and Javascript. How can I reliably map Europe/London to GMT Standard Time, along with all the other possible timezones in the world?

My options to get around this so far are:

• Use a hard-coded lookup table - could break if timezone info changes in the future.
• Send all the pre-aggregated data to the browser, apply the timezone to the date and then aggregate the data on the client - I want to avoid moving too much data around unnecessarily.

Is there any other way to do this that I'm missing?

Answer 1

Given the history of questions in your user profile, I'll assume you are using .NET in your application layer to query your SQL Server. If this is not the case, please comment and I will adjust my answer accordingly.

The core of your question is addressed by How to translate between Windows and IANA time zones?. In short, you can use my TimeZoneConverter library in your application layer to convert the IANA time zone name coming from Intl in the browser, to a Windows time zone name that SQL Server will recognize.

However, I'll also add a few points on the SQL part of your question:

• In many cases, you're better off doing time zone conversions in your application layer than in SQL Server. .NET's TimeZoneInfo class can handle the conversions and understands IANA identifiers when running on Linux. You can use TZConvert.GetTimeZoneInfo from TimeZoneConverter to work with them when running on Windows. (Or alternatively, you could use Noda Time for the conversions.)

• If you are working with a relatively small number of data points, query them all from SQL without grouping, then in your application layer convert them and then group and perform the aggregations there. That will come with memory and network overhead - but will make your queries more efficient and conversions easier.

• If you are working with a larger data set, then the aggregation and conversion indeed must be done at query time. Use TZConvert.IanaToWindows from TimeZoneConverter, and pass that as a parameter to your query to be used with AT TIME ZONE.

• To prevent the query engine having to scan the entire table, you'll want an index over your UTC value. You'll also want a WHERE clause that uses that raw value, so your query is "sargable". Thus, you'll need to convert the desired query range from the client's time zone to UTC before executing the query.

• When the source value is a datetime or datetime2, you need to use AT TIME ZONE twice. For example SomeDateTimeValue AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time'. The first will create a datetimeoffset using the time zone given, assigning the correct offset (+00:00) while keeping the date and time values. The second will convert that datetimeoffset to another datetimeoffset in the given time zone, adjusting the date, time, and offset accordingly.

Here is an example to demonstrate the SQL side of things. I've set up a temp table with some additional values to make clear what is included and excluded from the final query.

--Temp table for demo purposes
CREATE TABLE #MyTable (UtcDateTime datetime2, MyValue int)
INSERT INTO #MyTable VALUES ('2020-10-01 06:00:00', 1000) -- excluded
INSERT INTO #MyTable VALUES ('2020-10-01 07:00:00', 100)  -- included
INSERT INTO #MyTable VALUES ('2020-10-01 10:00:00', 24)   -- included
INSERT INTO #MyTable VALUES ('2020-10-01 15:00:00', 53)   -- included
INSERT INTO #MyTable VALUES ('2020-10-02 12:00:00', 26)   -- included
INSERT INTO #MyTable VALUES ('2020-10-03 06:00:00', 100)  -- included
INSERT INTO #MyTable VALUES ('2020-10-03 07:00:00', 1000) -- excluded
 
-- These are the input values of the query
DECLARE @ClientTZ varchar(50) = 'Pacific Standard Time' -- Converted from 'America/Los_Angeles' in the application layer with TimeZoneConverter
DECLARE @FromClientDate date  = '2020-10-01'
DECLARE @ToClientDate date = '2020-10-02'


-- START OF QUERY

-- First you'll need to know the UTC datetimes of the range you are querying over.
-- This pair of values creates a half-open UTC datetime range to be used in the WHERE clause below.
DECLARE @FromUtcDateTime datetime2 = CAST(@FromClientDate AS datetime2) AT TIME ZONE @ClientTZ AT TIME ZONE 'UTC'
DECLARE @ToUtcDateTime datetime2 = CAST(DATEADD(DAY, 1, @ToClientDate) AS datetime2) AT TIME ZONE @ClientTZ AT TIME ZONE 'UTC'

-- Now you can do the query.  The inner subquery does the filtering and conversion.  The outer query does the aggregation.
SELECT ClientDate, AVG(MyValue) as AvgValue
FROM (
    SELECT CAST(UtcDateTime AT TIME ZONE 'UTC' AT TIME ZONE @ClientTZ AS date) as ClientDate, MyValue
    FROM #MyTable
    WHERE UtcDateTime >= @FromUtcDateTime AND UtcDateTime < @ToUtcDateTime
) q
GROUP BY q.ClientDate

-- END OF QUERY

-- Drop the temp table for demo cleanup
DROP TABLE #MyTable