SQL Server 作业/计划 - 美国与英国夏令时调整 [英] SQL Server Jobs / Schedules - US vs. UK Daylight Savings Adjustment
问题描述
SQL Server 作业/计划 - 美国与英国的夏令时调整
SQL Server Jobs / Schedules - US vs. UK Daylight Savings Adjustment
我们有一台位于英国的服务器，它需要在 16:30(美国中部时间 - 这可能看起来很奇怪，但由于某些数据的可用性)需要运行 SQL 代理作业.通常这不会成为问题，因为英国和美国之间的时差是 6 小时，所以我们将工作安排在 22:30.
We have a UK based server which needs to run a SQL Agent Job at 16:30 (US Central time – this may seem strange but it because of the availability of some data). Usually this wouldn’t be a problem because the time difference between the UK and US is 6 hours so we schedule our job for 22:30.
但由于英国和美国在 3 月和 11 月为夏令时在不同时间调整时钟，因此英国和美国之间的时差为 5 小时的时间为 2 周.
But because the UK and US adjust their clocks at different times in March and November for daylight savings, there is a 2 week period when the time difference between the UK and US is 5 hours.
我想实现一种方法，使 SQL Server 可以识别这种时差变化，然后使用 sp_update_schedule 之类的东西重新安排它自己的作业.
I would like to implement a way in which SQL Server could identify this change of time difference and then re-schedule the job it’s self using something like sp_update_schedule.
我采用的一种方法是在美国服务器上获取当前时间戳，然后将其与当前英国时间进行比较.如果时差为 5 小时，则将作业重新安排到 21:30，如果时差为 6 小时，则将作业重新安排到 22:30.
One approach I have is to get the current timestamp on a US based server and then compare that with the current UK time. If the time difference is 5 hours then reschedule the job to 21:30, if the time difference is 6 hours then reschedule the job to 22:30.
但无论如何都可以提出另一种解决方案，也许一个不涉及在美国服务器上获取当前时间戳并且理想情况下不必在表中存储夏令时调整日期列表的解决方案.也许更有活力?
But can anyway suggest another solution, perhaps one which wouldn’t involve get the current timestamp on the US based server and ideally not having to store a list of daylight savings adjustment dates in a table. Something more dynamic maybe?
推荐答案
实际上，您似乎已经提到了两个可行的解决方案:询问该时区的服务器或存储关键日期列表.关于原因的派对路线在下面的第一个链接中.您可以实现自己的函数(必须存储日期列表或向服务器询问偏移量).或者您可以获得时区信息的公共域列表(如下面的第二个链接).还有几个 Web 服务将返回偏移量.Oracle 实现了时区数据库以在命名时区之间进行转换，因此如果您已经支持两者，则可以这样做.
Actually, it looks like you've mentioned the two viable solutions: Ask a server on that timezone or store a list of critical dates. The party line as to why at the first link below. You can implement your own function (which would have to either store a list of dates or ask a server for the offset). Or you can get a public domain list of time zone information (as at the second link below). There are also several web services which will return offsets. Oracle implements that database of time zones to convert between named time zones, so if you already support both you could do that.
有一个 C# 实现，您可以在第三个链接处将其包装在 CLR 函数中.
There is a C# implementation that you could wrap in CLR functions at the third link.
http://www.twinsun.com/tz/tz-link.htm
这篇关于SQL Server 作业/计划 - 美国与英国夏令时调整的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
