使用時區調整將字元串轉換為日期

我正在嘗試將stirngs轉換為mysql中的日期

Thu Oct 23 16:46:47 2014 +02:00

我不確定如何處理 +02:00，其餘的很簡單

update logs
set date = str_to_date(date_raw, '%a %b %e %T %Y')

這返回

Truncated incorrect datetime value: 'Thu Oct 23 16:46:47 2014 +02:00'

有任何想法嗎？

儲存函式怎麼樣？

DELIMITER $$

DROP FUNCTION IF EXISTS `ts_from_offset` $$
CREATE FUNCTION `ts_from_offset`(in_ts TINYTEXT) RETURNS datetime
NO SQL
DETERMINISTIC
BEGIN

-- Thu Oct 23 16:46:47 2014 +02:00

-- this function takes an input timestamp value with an offset formatted as above,
-- and converts it to the equivalent MySQL datetime value, expressed in the current session's
-- time zone.  Since this is also the timezone that columns in the TIMESTAMP data type expect,
-- this causes the input value to be stored correctly in the native TIMESTAMP format, which is.
-- UTC under the hood.

-- if you are taking the value here and stuffing it into a non-UTC DATETIME column, you need to have 
-- session @@time_zone set to the same zone in which that column should be stored, or use
-- CONVERT(ts_from_offset('input value'),'UTC','Your Desired Time Zone');

-- http://dba.stackexchange.com/questions/83898/converting-string-to-date-with-timezone-adjustment/84041#84041

DECLARE offset_string TINYTEXT DEFAULT NULL;
DECLARE date_string TINYTEXT DEFAULT NULL;
DECLARE offset_sign TINYINT DEFAULT NULL;
DECLARE offset_hours TINYINT DEFAULT NULL;
DECLARE offset_minutes TINYINT DEFAULT NULL;

SET offset_string = SUBSTRING_INDEX(in_ts,' ',-1);

SET in_ts = LEFT(in_ts, LENGTH(in_ts) - 1 - LENGTH(offset_string));

SET offset_sign = IF(SUBSTRING(offset_string FROM 1 FOR 1) = '+', -1, +1); # we need to flip the sign, to "back out" the offset to get a time in UTC
SET offset_hours = CAST(SUBSTRING(offset_string FROM 2 FOR 2) AS SIGNED) * offset_sign;
SET offset_minutes = CAST(SUBSTRING(offset_string FROM 5 FOR 2) AS SIGNED) * offset_sign;
RETURN CONVERT_TZ(DATE_ADD(DATE_ADD(STR_TO_DATE(in_ts,'%a %b %e %T %Y'), INTERVAL offset_hours HOUR), INTERVAL offset_minutes MINUTE),'UTC',@@time_zone);

END $$

DELIMITER ;

---

範例輸出…

mysql> SET @@TIME_ZONE = 'UTC';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT ts_from_offset('Thu Oct 23 16:46:47 2014 +02:00');
+---------------------------------------------------+
| ts_from_offset('Thu Oct 23 16:46:47 2014 +02:00') |
+---------------------------------------------------+
| 2014-10-23 14:46:47                               |
+---------------------------------------------------+
1 row in set (0.00 sec)

沒有保修，但似乎這應該可以解決問題。

假設 v5.6（而且我認為這沒有改變），這可能是您需要知道的（稍微拆分一下，MySQL 的文件傾向於編寫大量文本）：

http://dev.mysql.com/doc/refman/5.6/en/datetime.html

MySQL 將 TIMESTAMP 值從目前時區轉換為 UTC 進行儲存，並從 UTC 轉換回目前時區進行檢索。（這不會發生在其他類型，例如 DATETIME。）

預設情況下，每個連接的目前時區是伺服器的時間。可以基於每個連接設置時區。

只要時區設置保持不變，您就可以返回儲存的相同值。如果您儲存一個 TIMESTAMP 值，然後更改時區並檢索該值，則檢索到的值與您儲存的值不同。發生這種情況是因為沒有使用同一時區進行雙向轉換。

目前時區可用作 time_zone 系統變數的值。

因此，如果您在會話設置為 UTC 時儲存一個值 (1023)，並從設置為 EST 的會話中檢索它，您應該返回與 -0500 (0523) 相同的值

引用自：https://dba.stackexchange.com/questions/83898