Converting string to date with timezone adjustment I am attempting to convert stirngs to dates in mysql

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


Im unsure how to handle the +02:00, The rest is simple enough

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


This returns

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


Any ideas?
How about a stored function?

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 ;




Example output...

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

mysql&gt; 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)


No warranty, but it seems like this should do the trick.