Why are there no "Daylight Time" time zones in SQL Server?

Question

I am converting the times of some datetimeoffset columns from UTC to Eastern. I am doing this via the command

UPDATE MyTable SET MyColumn = MyColumn AT TIME ZONE 'Eastern Standard Time'

This seemed to be working correctly (times were shifted 4 hours earlier and now have a -04:00 offset, exactly what I wanted), but then I realized that it's not EST, it's EDT right now.

SELECT * FROM sys.time_zone_info 

gives me a list of time zones, but there is no "Eastern Daylight Time", only "Eastern Standard Time." There is a column "is_current_dst" which seems correct. But as far as I know, it doesn't make any sense to talk about the time zone "Eastern Standard Time" being in DST or not. Eastern Standard Time means it is not Daylight Saving Time. "Eastern Daylight Time" means it is Daylight Saving Time. To me this seems like getting a list of animals, and instead of having "Dog" and "Cat" on the list, there's only "Dog", with a "isCat" flag!

I'm confused about why the time zone is called "Eastern Standard Time" (and why my update statement above converts the time to Eastern Daylight Time (aka UTC -04:00).

Is that the name of the time zone is incorrect, and it should really just be something like "Eastern Time" with the flag telling if it's currently EST or EDT?

Or am I just completely confused?

Answer 1

The time zone list actually comes from an API call to the OS, But SQL Server doesn't expose all of the columns/attributes available. Unfortunately, this means that we're depending on the design decisions of Windows, which treats Eastern Standard Time and Eastern Daylight Time as one time zone that changes names and UTC offset.

If you look at the output of the PowerShell command Get-TimeZone, you'll see something like this:

Id                         : Eastern Standard Time
DisplayName                : (UTC-05:00) Eastern Time (US & Canada)
StandardName               : Eastern Standard Time
DaylightName               : Eastern Daylight Time
BaseUtcOffset              : -05:00:00
SupportsDaylightSavingTime : True

However, if you look at the SQL Server DMV, you'll see only 3 columns:

SELECT *
FROM sys.time_zone_info
WHERE name LIKE '%Aus%';

What SQL Server shows as the name corresponds to the StandardName from the OS/PowerShell. SQL Server does know if it is currently the time zone is currently experiencing Daylight Saving Time, but it doesn't have a concept of the DaylightName... It exclusively uses the StandardName

I recently played around with this, including caching the data from PowerShell into a table in SQL Server, to make it easier to map DST time zone names to the standard ones used by SQL Server. You'll still need to use the standard name with AT TIME ZONE, but can at least have the full set of data available. You can read more about that here