API 位于https://timezonedb.com/references/convert-time-zone https://timezonedb.com/references/convert-time-zone确实是一个在全球范围内获得正确信息的好地方time, timezone, and 两个位置之间的时区偏移,考虑过去/未来夏令时的变化。
您建议的仅指定时区缩写的方法存在问题(例如“将 PST 转换为 EST”)这个 API 会占用你的区域字面上地,即使它们是不正确的。
所以,如果多伦多目前处于EDT https://time.is/EDT但你指定EST https://time.is/EST,您可能会得到不正确的时间。使用“全名”,例如(UTC-08:00) Pacific Time (US & Canada)
会有同样的问题。
解决这个问题的方法是指定时区名称,例如America/Vancouver
(如所列here https://timezonedb.com/time-zones),或者使用适当的参数指定城市、国家和/或地区名称。
我编写了一个函数来解决这个问题,但它仅适用于某些国家(请参阅下文)。
去年万圣节晚上 11:11 温哥华时间 多伦多 是什么时候?
Result:(默认为XML
but 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 函数的示例:
圣诞节那天温哥华和柏林之间的时差是多少?
输入时间: 2018-12-25 00:00:00
= 温哥华本地 Unix 时间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 时间戳:
Unix 时间定义为“自 1970 年 1 月 1 日星期四 00:00:00 协调世界时间 (UTC) 以来经过的秒数。"
您可以在 Unix 和/或 UTC 或本地时间之间转换时间:epochconverter.com https://www.epochconverter.com/...该网站还提供多种编程语言的转换公式。
例如,转换公式Unix 时间转 GMT/UTC在Excel中是:
=(A1 / 86400) + 25569
您还可以下载静态文件(在SQL
or CSV
格式)here https://timezonedb.com/download而不是调用 API,并且该页面还有示例查询。However请谨慎使用:夏令时更容易出错(如上所述)。
我创建了一个虚拟帐户来获取示例中使用的“演示”,但您应该获得自己的(免费)密钥以供长期使用。 (如果它因过度使用而被锁定,我不承担任何责任!)
An good alternative Time Zone API is Google Maps Time Zone API https://developers.google.com/maps/documentation/timezone/intro. The difference is that you specify Latitude & Longitude. It seems to work just fine without a key You'll need to register https://developers.google.com/maps/documentation/timezone/get-api-key for a key.
6 月 1 日白宫的时区偏移是多少?
Result:
{
"dstOffset" : 0,
"rawOffset" : -18000,
"status" : "OK",
"timeZoneId" : "America/Toronto",
"timeZoneName" : "Eastern Standard Time"
}
偏移量将为 -18000 秒(-5 小时)。
确定夏令时何时生效
下面是我整理的一个函数,这样我就可以“信任”从不同 API 获取的夏令时 (DST) 值,但是(正如其他人所讨论的)规则没有模式plus国家不断变化,甚至世界某些地区的城镇不断变化,因此这只适用于以下国家:
- DST 于每年三月的第二个星期日开始
- DST 于每年 11 月的第一个星期日结束
The applicable countries are Bahamas, Bermuda, Canada, Cuba, Haiti, St. Pierre & United States. (Source: Daylight saving time by country https://en.wikipedia.org/wiki/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!
最后,还有一个替代方案是我用来轮询当前/未来/历史的 APIweather用于各种目的的数据 - 以及also恰好提供时区偏移——是黑暗天空。
它按纬度/经度查询,免费(每天最多 1000 次调用),并提供“超准确的天气数据”(在美国更是如此,它可以预测天气变化minute并致平方码!- 但我在加拿大西海岸看到的不可预测的情况相当准确!)
响应仅采用 JSON 格式,最后一行是时区偏移与 UTC/GMT time.
DarkSky 示例调用:
https://api.darksky.net/forecast/85b57f827eb89bf903b3a796ef53733c/40.70893,-74.00662 https://api.darksky.net/forecast/85b57f827eb89bf903b3a796ef53733c/40.70893,-74.00662
预计 Stack Overflow 总部未来 60 小时都会下雨。 ☂
...但我不知道,到目前为止看起来真是美好的一天! ☀
(flag) https://i.stack.imgur.com/kzcWB.gif