如何从时区名称获取时区偏移量 [英] How to get timezone offset from the timezone name

问题描述

我在 SQL Server 数据库中保存了一个时区名称，例如 America/Vancouver.我想从 SQL 中的时区名称获取 UTC 偏移量，例如 America/Vancouver 具有 -08:00 偏移量.那么如何在 SQL 中编写查询呢?非常感谢您的帮助

I have a timezone name like America/Vancouver saved in a SQL Server database. I want to get the UTC offset from the timezone name in SQL like America/Vancouver has -08:00 offset. So how can I write a query in SQL? Your help is much appreciated

推荐答案

遗憾的是，SQL Server 尚不直接支持 IANA 时区标识符(如 America/Vancouver).目前，最好的选择是将 IANA 标识符转换为应用层中相应的 Windows 时区标识符，并将其存储在 SQL Server 中.

Unfortunately SQL Server doesn't yet support IANA time zone identifiers (like America/Vancouver) directly. For now, the best option is to convert from the IANA identifier to a corresponding Windows time zone identifier in your application layer, and store that in your SQL Server.

例如，如果您在应用层中运行 .NET，则可以使用我的 TimeZoneConverter 项目:

For example, if you are running .NET in your application layer you can use my TimeZoneConverter project:

string tz = TZConvert.IanaToWindows("America/Vancouver");
// Result:  "Pacific Standard Time"

然后你可以使用AT TIME ZONE SQL Server 代码中的函数:

Then you can use the AT TIME ZONE function in your SQL Server code:

SELECT sysdatetimeoffset() AT TIME ZONE 'Pacific Standard Time'

以上将为您提供给定时区的当前日期、时间和偏移量，您可以将其用作查询用户可用性的基础(在问题评论中提到).

The above will give you the current date, time, and offset in the given time zone, which you can use as the basis for your query against the user's availability (mentioned in the question comments).

请记住，根据夏令时是否生效，偏移调整会有所不同.尽管标准"一词在 Windows 标识符中，确实会在适用的情况下考虑 DST.

Keep in mind that the offset adjustment will be different depending on whether daylight saving time is in effect or not. Despite the word "Standard" in the Windows identifier, DST is indeed taken into account where applicable.

或者，如果出于某种原因您必须完全在 SQL Server 中执行此操作而不转换为 Windows 时区，那么您将需要依赖诸如我的 SqlServerTimeZoneSupport 项目.那个有点旧而且维护得不好，所以如果你能帮忙的话，我建议不要使用这种方法.

Alternatively, if for some reason you must do this entirely in SQL Server without converting to Windows time zones, then you will need to rely on projects such as my SqlServerTimeZoneSupport project. That one is a bit old and not very well maintained, so I recommend against that approach if you can help it.

这篇关于如何从时区名称获取时区偏移量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！