SQL 转换为 GMT 日期时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1404193/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Convert to GMT DateTime
提问by Lici
i wan't to convert my records entries from dateTtime+OFFSET to dateTtimeZ directly
我不想将我的记录条目从 dateTtime+OFFSET 直接转换为 dateTtimeZ
(2008-05-11T15:30:00+2--->2008-05-11T13:30:00Z)
(2008-05-11T15:30:00+2--->2008-05-11T13:30:00Z)
with sql functions.
带sql函数。
Don't know how to do this :-(
不知道如何做到这一点:-(
I need to implement this using MySql prefering not using stored procs
我需要使用 MySql 来实现这一点，而不是使用存储过程
Thx
谢谢
回答by José Cláudio
Try this
尝试这个
To convert from GMT to local time:
要将 GMT 转换为当地时间：
select DATEADD(hour,DATEDIFF (hour, GETUTCDATE(), GETDATE()),MyGmtDateTime) as LocalDateTime
To convert from local time to GMT:
要将当地时间转换为 GMT：
select DATEADD(hour,DATEDIFF (hour, GETDATE(), GETUTCDATE()),MyLocalDateTime) as GmtDateTime
回答by Lici
If I understood your question correctly, there is no way to find out the time zone from time+offset because the mapping is not unique. Several time zones may have the same offset.
如果我正确理解您的问题，则无法从 time+offset 找出时区，因为映射不是唯一的。多个时区可能具有相同的偏移量。
Look at this example where multiple time zone have the same offset.
看看这个例子，其中多个时区具有相同的偏移量。
(GMT-06:00) Saskatchewan
(GMT-06:00) Guadalajara, Mexico City, Monterrey - Old
(GMT-06:00) Guadalajara, Mexico City, Monterrey - New
(GMT-06:00) Central Time (US & Canada)
(GMT-06:00) Central America
ADDED: Now I see you asked for something else.
补充：现在我看到你要求别的东西。
Okay, if the offset in your datetimes is always the same, you can try this transformation.
好的，如果您的日期时间中的偏移量始终相同，您可以尝试这种转换。
DECLARE @DateTimeWithOffset datetimeoffset
DECLARE @JustDateTime datetime
SET @DateTimeWithOffset = '2008-05-11 15:30:00 + 02:00'
SELECT @DateTimeWithOffset = SWITCHOFFSET (@DateTimeWithOffset, '00:00')
SELECT @JustDateTime = CAST (@DateTimeWithOffset AS datetime)
This is to give you the idea. I don't have an SQL 2008 right at hand so I haven't tested that. May not work.
这是给你的想法。我手头没有 SQL 2008，所以我没有测试过。可能不起作用。
回答by PaulMcG
I'm pretty rusty with my SQL built-in functions, but in the absence of any standard function, you could write a stored procedure to do the work. Then you could invoke it as part of your SELECT statement:
我对我的 SQL 内置函数非常生疏，但是在没有任何标准函数的情况下，您可以编写一个存储过程来完成这项工作。然后您可以将它作为 SELECT 语句的一部分调用：
SELECT to_GMT(invoice_date) from INVOICES
回答by devstuff
It is not clear from your question, but I assume you have the values as a string. If so, then extract everything except the offset as a datetime, also get the offset as a datetime, then use the sign to calculate the final result (T-SQL, 2005):
从您的问题中不清楚，但我假设您将值作为字符串。如果是这样，则将除偏移量之外的所有内容提取为 a datetime，也将偏移量作为 a datetime，然后使用符号计算最终结果（T-SQL，2005）：
DECLARE @withOffset varchar(30);
SET @withOffset = '2008-05-11T15:30:00+2:00';
PRINT N'Original: ' + CAST(@withOffset AS nvarchar);
DECLARE @dt datetime;
SET @dt = CONVERT(datetime, LEFT(@withOffset, 19), 126);
PRINT N'dt=' + CONVERT(nvarchar, @dt, 127);
DECLARE @ofs datetime;
SET @ofs = CONVERT(datetime, SUBSTRING(@withOffset, 21, LEN(@withOffset) - 21), 108);
PRINT N'ofs=' + CAST(@ofs AS nvarchar);
IF (SUBSTRING(@withOffset, 19, 1) = '+')
BEGIN
SET @dt = DATEADD(hour, DATEPART(hour, @ofs), @dt);
SET @dt = DATEADD(minute, DATEPART(minute, @ofs), @dt);
END
ELSE
BEGIN
SET @dt = DATEADD(hour, -DATEPART(hour, @ofs), @dt);
SET @dt = DATEADD(minute, -DATEPART(minute, @ofs), @dt);
END;
PRINT N'dt=' + CONVERT(nvarchar, @dt, 127);
回答by Vincent Malgrat
the solution will likely depend of your RDBMS. In Oracle this would work:
解决方案可能取决于您的 RDBMS。在 Oracle 中，这将起作用：
SQL> SELECT to_timestamp_tz('2008-05-11T15:30:00+2',
2 'yyyy-mm-dd"T"hh24:mi:ssTZH')
3 AT TIME ZONE 'GMT' gmt_time
4 FROM dual;
GMT_TIME
----------------------------------
11/05/08 13:30:00,000000000 GMT
回答by hexangel616
Use the SQL function CONVERT_TZ(dt,from_tz,to_tz).
使用 SQL 函数CONVERT_TZ(dt,from_tz,to_tz)。
CONVERT_TZ() converts a datetime value dt from the time zone given by from_tz to the time zone given by to_tz and returns the resulting value.
CONVERT_TZ() 将日期时间值 dt 从 from_tz 给定的时区转换为 to_tz 给定的时区，并返回结果值。
Example:
例子：
UPDATE this_table
SET this_table_date_gmt = (
SELECT CONVERT_TZ(this_date, '+00:00', '-02:00')
)
Reference : https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_convert-tz
参考：https: //dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_convert-tz
