vba - 在任意时区之间转换

Question

我正在尝试找到一种简单而强大的方法来在任意时区之间转换时间。

这个:http://www.cpearson.com/excel/TimeZoneAndDaylightTime.aspx仅解释如何在我的(当前)TZ 和另一个 TZ 之间进行转换。

这两篇 SO 文章( Getting Windows Time Zone Information (C++/MFC) 和 How do you get info for an arbitrary time zone in Windows? )讨论了从注册表获取信息。

这听起来有点过于复杂和耗时；此外，Windows 似乎以“全名”存储 TZ(例如 (UTC-08:00) Pacific Time (US & Canada))，我宁愿使用缩写来引用 TZ(例如美国东部夏令时间 (EDT)。此外，依赖 Windows 注册表也可能不安全:不同的用户可能有不同的版本，有些可能不是最新的。这意味着由两个人运行的报告可能会提供两种不同的结果!

有没有一种更简单、更稳健的方法？编写查找表可能会在一段时间内起作用，但当政府决定废除 DST 或更改其他任何内容时，它就会被破坏。

也许可以从互联网上获取 TZ 列表并解析它？这样足够安全吗？

更新 1

我已经进行了研究并探索了可能性，但这个问题并不像看起来那么微不足道。如果您认为该函数应类似于 bTime = aTime + 3，那么请重新考虑。时区和夏令时处于不断变化的状态。

阅读此内容以供引用:list of pending / proposed timezone changes 。请注意，有些国家/地区实际上正在更改其时区，而不仅仅是 DST 设置!巴西将时钟更改日期改为冬令时!所有这些更改都会很快破坏静态查找表。

更新2

我并不是在研究一个快速而肮脏的黑客，我可以自己想出这个。我不想写下一些东西然后就忘记了；我想创建一个函数，其他人可以安全地将其用于不同的内部项目，而无需维护噩梦。硬编码已知偶尔会更改的常量是一种非常糟糕的软件设计(想想由一段非常非常旧的代码引起的千年虫错误)。

更新3

这个数据库看起来不错(虽然我不确定它是否足够稳定):https://timezonedb.com/api 。他们甚至有一个 TZ 转换调用 - 正是我所需要的!我可能会尝试从 VBA 解析 XML 并分享我的结果。

Answer 1

API 位于 https://timezonedb.com/references/convert-time-zone确实是获取正确的全局时间、时区和两个位置之间的时区偏移的好地方，考虑到过去/ future 的夏令时储蓄变化。

您建议的仅指定时区缩写(例如“将 PST 转换为 EST”)的问题是，此 API 按字面意思获取您的时区，即使如果它们不正确。

所以，如果多伦多当前位于 EDT但您指定 EST ，您可能会得到不正确的时间。使用“全名”，例如 (UTC-08:00) Pacific Time (US & Canada) 也会出现同样的问题。

解决这个问题的方法是指定时区名称，例如 America/Vancouver (如列出的 here )，或者使用适当的参数指定城市、国家和/或地区名称。

我编写了一个函数来解决这个问题，但它仅适用于某些国家(请参阅下文)。

<小时/>

去年万圣节晚上 11:11 温哥华时间在多伦多是几点？

http://api.timezonedb.com/v2/convert-time-zone?key=94RKE4SAXH67&from=America/Vancouver&to=America/Toronto&time=1509516660

结果:(默认为 XML，但 JSON 也可用。)

<result>
    <status>OK</status>
    <message/>
    <fromZoneName>America/Vancouver</fromZoneName>
    <fromAbbreviation>PDT</fromAbbreviation>
    <fromTimestamp>1509516660</fromTimestamp>
    <toZoneName>America/Toronto</toZoneName>
    <toAbbreviation>EDT</toAbbreviation>
    <toTimestamp>1509527460</toTimestamp>
    <offset>10800</offset>
</result>

<小时/>

以编程方式获取数据:

您必须决定多种选项和查找方法，但这里有一个使用 VBA 函数的示例:

What will be the time difference between Vancouver & Berlin on Christmas Day?

Input Time: 2018-12-25 00:00:00 = Vancouver Local Unix time 1545724800

Function GetTimeZoneOffsetHours(fromZone As String, _
            toZone As String, UnixTime As Long) As Single

    Const key = "94RKE4SAXH67"
    Const returnField = "<offset>"
    Dim HTML As String, URL As String
    Dim XML As String, pStart As Long, pStop As Long

    URL = "http://api.timezonedb.com/v2/convert-time-zone?key=" & key & _
        "&from=" & fromZone & "&to=" & toZone & "&time=" & UnixTime
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", URL, False
        .Send
        XML = .ResponseText
    End With

    pStart = InStr(XML, returnField)
    If pStart = 0 Then
        MsgBox "Something went wrong!"
        Exit Function
    End If

    pStart = pStart + Len(returnField) + 1
    pStop = InStr(pStart, XML, "</") - 1
    GetTimeZoneOffsetHours = Val(Mid(XML, pStart, pStop - pStart)) / 60
End Function


Sub testTZ()
    Debug.Print "Time Zone Offset (Vancouver to Berlin) = " & _
        GetTimeZoneOffsetHours("America/Vancouver", _
        "Europe/Berlin", 1545724800) & " hours"
End Sub

<小时/>

Unix/UTC Timestamps:

Unix time is defined as "the number of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970."

You can convert times between Unix and/or UTC or Local time at: epochconverter.com ... the site also has conversion formulas for several programming languages.

For example, the formua to convert Unix time to GMT/UTC in Excel is:

=(A1 / 86400) + 25569

<小时/>

您还可以下载静态文件(SQL 或 CSV 格式)here而不是调用 API，并且该页面还有示例查询。 但是请谨慎使用:夏令时更容易出错(如上所述)。

我创建了一个虚拟帐户来获取示例中使用的“演示”，但您应该获得自己的(免费) key 以供长期使用。 (如果它因过度使用而被锁定，我不承担任何责任!)

<小时/>

一个很好的替代时区 API 是 Google Maps Time Zone API 。不同之处在于您指定纬度和经度。 它似乎可以在没有 key 的情况下正常工作您需要register一把 key 。

What will the Time Zone Offset be on June 1st at the White House?

https://maps.googleapis.com/maps/api/timezone/json?location=38.8976,-77.0365&timestamp=1527811200&key={YourKeyHere}

结果:

{
   "dstOffset" : 0,
   "rawOffset" : -18000,
   "status" : "OK",
   "timeZoneId" : "America/Toronto",
   "timeZoneName" : "Eastern Standard Time"
}

The Offset will be -18000 seconds (-5 hours).

<小时/>

确定夏令时何时生效

下面是我整理的一个函数，这样我就可以“信任”从不同 API 获取的夏令时 (DST) 值，但是(正如其他人所讨论的)规则没有模式加 不断变化的国家/地区，甚至世界某些地区的城镇/城镇，因此这只适用于以下国家:

• 夏令时于每年三月的第二个星期日开始
• 夏令时于每年 11 月的第一个星期日结束

适用的国家包括巴哈马、百慕大、加拿大、古巴、海地、圣皮埃尔和美国。 _{(来源: Daylight saving time by country **)}

Function IsDST(dateTime As Date) As Boolean

    'Returns TRUE if Daylight Savings is in effect during the [dateTime]
    'DST Start (adjust clocks forward) Second Sunday March at 02:00am
    'DST end (adjust clocks backward) First Sunday November at 02:00am

    Dim DSTStart As Date, DSTstop As Date

    DSTStart = DateSerial(Year(dateTime), 3, _
        (14 - Weekday(DateSerial(Year(dateTime), 3, 1), 3))) + (2 / 24)
    DSTstop = DateSerial(Year(dateTime), 11, _
        (7 - Weekday(DateSerial(Year(dateTime), 11, 1), 3))) + (2 / 24)
    IsDST = (dateTime >= DSTStart) And (dateTime < DSTstop)

End Function

以及一些如何使用函数 IsDST* 的示例:

Public Function UTCtoPST(utcDateTime As Date) As Date
    'Example for 'PST' time zone, where Offset = -7 during DST, otherwise if -8

    If IsDST(utcDateTime) Then
        UTCtoPST = utcDateTime - (7 / 24)
    Else
        UTCtoPST = utcDateTime - (8 / 24)
    End If

End Function


Function UTCtimestampMStoPST(ByVal ts As String) As Date
    'Example for 'PST', to convert a UTC Unix Time Stamp to 'PST' Time Zone

    UTCtimestampMStoPST = UTCtoPST((CLng(Left(ts, 10)) / 86400) + 25569)

End Function

* _{Note that function IsDST is incomplete: It does not take into account the hours just before/after IsDST takes actually effect at 2am. Specifically when, in spring, the clock jumps forward from the last instant of 01:59 standard time to 03:00 DST and that day has 23 hours, whereas in autumn the clock jumps backward from the last instant of 01:59 DST to 01:00 standard time, repeating that hour, and that day has 25 hours ...but, if someone wants to add that functionality to update the function, feel free! I was having trouble wrapping my head around that last part, and didn't immediately need that level of detail, but I'm sure others would appreciate it!}

<小时/>

最后，还有一个替代方案是我用于出于各种目的轮询当前/ future /历史天气数据的 API，并且还恰好提供时区偏移量 — 是 DarkSky。

它按纬度/经度查询，免费(每天最多 1000 次调用)，并提供“超准确的天气数据”(在美国更是如此，它可以预测低至分钟和平方码! - 但我在不可预测的加拿大西海岸看到的相当准确!)

响应仅采用 JSON 格式，最后一行是时区偏移与 UTC/GMT 时间。

DarkSky Sample Call:

https://api.darksky.net/forecast/85b57f827eb89bf903b3a796ef53733c/40.70893,-74.00662

It says it's supposed to rain for the next 60 hours at Stack Overflow's Head Office. ☂

...but I dunno, it looks like a pretty nice day so far! ☀

_^(flag)