转换SQL中的Windows时间设置字符串

Question

我有一个Windows时间信息字符串，需要将其传递给存储过程，以计算出用户时区中的当前时间。

我在应用程序中使用的日期信息是：

澳大利亚东部标准时间

从这里开始：

https://msdn.microsoft.com/en-us/library/ms912391%28v=winembedded.11%29.aspx

在SQL中，有没有用它来计算他们的时间？

Answer 1

在国际应用程序中存储日期时间值的主要策略是将其转换并存储为UTC（"协调世界时"）等效值。对于著名的Microsoft网页，UTC时间与GMT（"格林尼治标准时间"）时间相同。这样，所有存储的时间值都可以相互比较，因为它们代表相同的通用时区。

为著名的Microsoft网页列出的表格有两个条目，分别为"澳大利亚东部标准时间"（简称"AEST"）。

A.U.S. Eastern Standard Time = (GMT+10:00) Canberra, Melbourne, Sydney
E. Australia Standard Time   = (GMT+10:00) Brisbane

这意味着。。。

UTC (or GMT) = AEST - 10:00

假设您的服务器设置为澳大利亚东部标准时间，下面的SELECT"in SQL"将产生当前UTC时间。

SELECT GETDATE()-10 AS 'Current UTC DateTime'

（另一个需要考虑的选项是将服务器设置为UTC时间，然后在存储当前日期时间值之前无需进行转换。）

但是，您的用户可能希望以本地时区格式查看他们记录的日期时间值。如果您将它们的时区属性与用户数据一起存储，就可以做到这一点。将UTC日期时间转换为用户的本地时间通常是在应用程序级别完成的。只要应用程序知道日期时间值与UTC值分配一起存储，并且应用程序知道用户的本地时区，应用程序就可以执行此操作。

Answer 2

看起来答案是——糟糕的设计。我正在存储Windows用于确定时区的"显示名称"。这使得在SQL中处理时区变得困难，如果我的"时区"从.Net调用中得到偏移量的话。

因此，我根据基于Widnows的时区信息生成了一个表，并在这里分享创建的内容，希望它对其他人有用。然后，在我的SQL中，我可以很容易地获取偏移量。

DROP TABLE z_timezone_type -- If it exists.
Go

创建引用表。

--CREATE THE TABLE
CREATE TABLE [dbo].[z_timezone_type] (
  [timezone_type_id]  INT              IDENTITY (1, 1) NOT NULL,
  [display]           VARCHAR(100) NOT NULL,
  [standard]          VARCHAR (100)    NOT NULL,
  [has_dst]           BIT  NOT NULL,
  [utc_offset]        INT NOT NULL
CONSTRAINT [pk_timezone_type] PRIMARY KEY CLUSTERED ([timezone_type_id] ASC)
);
GO

插入所有时区。

INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC-08:00) Pacific Time (US & Canada)', 'Pacific Standard Time', 1, '-8')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC-07:00) Arizona', 'US Mountain Standard Time', 0, '-7')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC-07:00) Chihuahua, La Paz, Mazatlan', 'Mountain Standard Time (Mexico)', 1, '-7')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC-07:00) Mountain Time (US & Canada)', 'Mountain Standard Time', 1, '-7')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC-06:00) Central America', 'Central America Standard Time', 0, '-6')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC-06:00) Central Time (US & Canada)', 'Central Standard Time', 1, '-6')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC-06:00) Guadalajara, Mexico City, Monterrey', 'Central Standard Time (Mexico)', 1, '-6')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC-06:00) Saskatchewan', 'Canada Central Standard Time', 0, '-6')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC-05:00) Bogota, Lima, Quito', 'SA Pacific Standard Time', 0, '-5')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC-05:00) Eastern Time (US & Canada)', 'Eastern Standard Time', 1, '-5')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC-05:00) Indiana (East)', 'US Eastern Standard Time', 1, '-5')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC-04:30) Caracas', 'Venezuela Standard Time', 0, '-4')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC-04:00) Asuncion', 'Paraguay Standard Time', 1, '-4')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC-04:00) Atlantic Time (Canada)', 'Atlantic Standard Time', 1, '-4')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC-04:00) Cuiaba', 'Central Brazilian Standard Time', 1, '-4')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC-04:00) Georgetown, La Paz, Manaus, San Juan', 'SA Western Standard Time', 0, '-4')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC-04:00) Santiago', 'Pacific SA Standard Time', 1, '-4')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC-03:30) Newfoundland', 'Newfoundland Standard Time', 1, '-3')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC-03:00) Brasilia', 'E. South America Standard Time', 1, '-3')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC-03:00) Buenos Aires', 'Argentina Standard Time', 1, '-3')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC-03:00) Cayenne, Fortaleza', 'SA Eastern Standard Time', 0, '-3')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC-03:00) Greenland', 'Greenland Standard Time', 1, '-3')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC-03:00) Montevideo', 'Montevideo Standard Time', 1, '-3')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC-02:00) Coordinated Universal Time-02', 'UTC-02', 0, '-2')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC-02:00) Mid-Atlantic', 'Mid-Atlantic Standard Time', 1, '-2')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC-01:00) Azores', 'Azores Standard Time', 1, '-1')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC-01:00) Cape Verde Is.', 'Cape Verde Standard Time', 0, '-1')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC) Casablanca', 'Morocco Standard Time', 1, '0')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC) Coordinated Universal Time', 'Coordinated Universal Time', 0, '0')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC) Dublin, Edinburgh, Lisbon, London', 'GMT Standard Time', 1, '0')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC) Monrovia, Reykjavik', 'Greenwich Standard Time', 0, '0')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna', 'W. Europe Standard Time', 1, '1')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+01:00) Belgrade, Bratislava, Budapest, Ljubljana, Prague', 'Central Europe Standard Time', 1, '1')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+01:00) Brussels, Copenhagen, Madrid, Paris', 'Romance Standard Time', 1, '1')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+01:00) Sarajevo, Skopje, Warsaw, Zagreb', 'Central European Standard Time', 'True', '1')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+01:00) West Central Africa', 'W. Central Africa Standard Time', 0, '1')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+01:00) Windhoek', 'Namibia Standard Time', 1, '1')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+02:00) Amman', 'Jordan Standard Time', 1, '2')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+02:00) Athens, Bucharest', 'GTB Standard Time', 1, '2')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+02:00) Beirut', 'Middle East Standard Time', 1, '2')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+02:00) Cairo', 'Egypt Standard Time', 1, '2')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+02:00) Damascus', 'Syria Standard Time', 1, '2')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+02:00) Harare, Pretoria', 'South Africa Standard Time', 0, '2')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+02:00) Helsinki, Kyiv, Riga, Sofia, Tallinn, Vilnius', 'FLE Standard Time', 'True', '2')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+02:00) Istanbul', 'Turkey Standard Time', 1, '2')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+02:00) Jerusalem', 'Jerusalem Standard Time', 1, '2')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+02:00) Minsk', 'E. Europe Standard Time', 1, '2')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+03:00) Baghdad', 'Arabic Standard Time', 1, '3')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+03:00) Kaliningrad', 'Kaliningrad Standard Time', 1, '3')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+03:00) Kuwait, Riyadh', 'Arab Standard Time', 0, '3')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+03:00) Nairobi', 'E. Africa Standard Time', 0, '3')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+03:30) Tehran', 'Iran Standard Time', 1, '3')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+04:00) Abu Dhabi, Muscat', 'Arabian Standard Time', 0, '4')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+04:00) Baku', 'Azerbaijan Standard Time', 1, '4')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+04:00) Moscow, St. Petersburg, Volgograd', 'Russian Standard Time', 1, '4')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+04:00) Port Louis', 'Mauritius Standard Time', 1, '4')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+04:00) Tbilisi', 'Georgian Standard Time', 0, '4')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+04:00) Yerevan', 'Caucasus Standard Time', 1, '4')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+04:30) Kabul', 'Afghanistan Standard Time', 0, '4')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+05:00) Islamabad, Karachi', 'Pakistan Standard Time', 1, '5')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+05:00) Tashkent', 'West Asia Standard Time', 0, '5')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+05:30) Chennai, Kolkata, Mumbai, New Delhi', 'India Standard Time', 0,'5')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+05:30) Sri Jayawardenepura', 'Sri Lanka Standard Time', 0, '5')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+05:45) Kathmandu', 'Nepal Standard Time', 0, '5')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+06:00) Astana', 'Central Asia Standard Time', 0, '6')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+06:00) Dhaka', 'Bangladesh Standard Time', 1, '6')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+06:00) Ekaterinburg', 'Ekaterinburg Standard Time', 1, '6')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+06:30) Yangon (Rangoon)', 'Myanmar Standard Time', 0, '6')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+07:00) Bangkok, Hanoi, Jakarta', 'SE Asia Standard Time', 0, '7')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+07:00) Novosibirsk', 'N. Central Asia Standard Time', 1, '7')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+08:00) Beijing, Chongqing, Hong Kong, Urumqi', 'China Standard Time', 0, '8')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+08:00) Krasnoyarsk', 'North Asia Standard Time', 1, '8')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+08:00) Kuala Lumpur, Singapore', 'Malay Peninsula Standard Time', 0, '8')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+08:00) Perth', 'W. Australia Standard Time', 1, '8')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+08:00) Taipei', 'Taipei Standard Time', 0, '8')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+08:00) Ulaanbaatar', 'Ulaanbaatar Standard Time', 0, '8')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+09:00) Irkutsk', 'North Asia East Standard Time', 1, '9')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+09:00) Osaka, Sapporo, Tokyo', 'Tokyo Standard Time', 0, '9')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+09:00) Seoul', 'Korea Standard Time', 0, '9')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+09:30) Adelaide', 'Cen. Australia Standard Time', 1, '9')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+09:30) Darwin', 'AUS Central Standard Time', 0, '9')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+10:00) Brisbane', 'E. Australia Standard Time', 0, '10')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+10:00) Canberra, Melbourne, Sydney', 'AUS Eastern Standard Time', 1, '10')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+10:00) Guam, Port Moresby', 'West Pacific Standard Time', 0, '10')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+10:00) Hobart', 'Tasmania Standard Time', 1, '10')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+10:00) Yakutsk', 'Yakutsk Standard Time', 1, '10')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+11:00) Solomon Is., New Caledonia', 'Central Pacific Standard Time', 0, '11')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+11:00) Vladivostok', 'Vladivostok Standard Time', 1, '11')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+12:00) Auckland, Wellington', 'New Zealand Standard Time', 1, '12')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+12:00) Coordinated Universal Time+12', 'UTC+12', 0, '12')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+12:00) Fiji', 'Fiji Standard Time', 1, '12')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+12:00) Magadan', 'Magadan Standard Time', 1, '12')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+12:00) Petropavlovsk-Kamchatsky - Old', 'Kamchatka Standard Time', 1, '12')
INSERT INTO z_timezone_type (display, standard, has_dst, utc_offset) VALUES ('(UTC+13:00) Nuku''alofa', 'Tonga Standard Time', 0, '13')

显示结果。

SELECT * FROM z_timezone_type