Azure Maps and SQL AT TIME ZONE incompatible time zone for Hawaii

Question

I just ran into an issue when using data that Azure Maps provides for timezones to attempt to get local time in SQL Server (Azure SQL) using AT TIME ZONE.

When I provide the lat/long data to Azure Maps for the Hawaiian location, the return says "Hawaii-Aleutian Standard Time":

"Names":{  
        "ISO6391LanguageCode":"en",
        "Generic":"Hawaii-Aleutian Time",
        "Standard":"Hawaii-Aleutian Standard Time",
        "Daylight":"Hawaii-Aleutian Daylight Time"
     },

In another part of my system I need the ability to determine a local time for that location in SQL Server so I am using AT TIME ZONE and injecting the Standard timezone. This works great for the US Timezones that I'm dealing with such as "Eastern Standard Time" or "Central Standard Time". When it gets to a Hawaiian location... it errors out and says that it isn't a valid time zone.

Reviewing the lists of timezones I've been able to find online for SQL Server it appears that Hawaii's timezone in SQL is "Hawaiian Standard Time".

Has anyone else run into this disconnect?

I'm thinking I will need to right some exception code to replace this Azure provided timezone with "Hawaiian Standard Time". Is there a better solution?

For reference here's a sample URL (minus key): https://atlas.microsoft.com/timezone/byCoordinates/json?subscription-key={key}&api-version=1.0&options=all&query=21.4500,-158.0054

Here's the full JSON returned:

{
  "Version": "2018g",
  "ReferenceUtcTimestamp": "2018-12-08T17:10:31.8007137Z",
  "TimeZones": [
    {
      "Id": "Pacific/Honolulu",
      "Aliases": [
        "Pacific/Johnston",
        "US/Hawaii"
      ],
      "Countries": [
        {
          "Name": "United States",
          "Code": "US"
        },
        {
          "Name": "US minor outlying islands",
          "Code": "UM"
        }
      ],
      "Names": {
        "ISO6391LanguageCode": "en",
        "Generic": "Hawaii-Aleutian Time",
        "Standard": "Hawaii-Aleutian Standard Time",
        "Daylight": "Hawaii-Aleutian Daylight Time"
      },
      "ReferenceTime": {
        "Tag": "HST",
        "StandardOffset": "-10:00:00",
        "DaylightSavings": "00:00:00",
        "WallTime": "2018-12-08T07:10:31.8007137-10:00",
        "PosixTzValidYear": 2018,
        "PosixTz": "HST+10"
      },
      "RepresentativePoint": {
        "Latitude": 21.306944444444444,
        "Longitude": -157.85833333333332
      },
      "TimeTransitions": [
        {
          "Tag": "HST",
          "StandardOffset": "-10:00:00",
          "DaylightSavings": "00:00:00",
          "UtcStart": "1947-06-08T12:30:00Z",
          "UtcEnd": "9999-12-31T23:59:59.9999999Z"
        }
      ]
    }
  ]
}

Answer 1

Ok, "Pacific/Honolulu" is the IANA code. According to some SQL docs it supports all Windows time zones while other docs say it only supports the time zones in the registry of the machine, which can vary from machine to machine. Physically checking my machine I can see SQL supports 137 time zones (Select * from sys.time_zone_info). However, according to Windows docs, it has 250 default time zones (plus a bunch of extended ones). There is definitely a disconnect some where but it is looking more like a Windows/SQL disconnect.

That said, it appears that "Hawaiian Standard Time" and "Hawaii-Aleutian Standard Time" are the same: https://www.timeanddate.com/time/zones/hast

However when it comes to daylight savings it gets much more complex: https://www.timeanddate.com/time/zones/hadt

I'll pass this thread on to the Azure Maps engineering and data team for deeper investigation.