Mysql - How to convert date from ISO 8601 format and save to mysql column?

Question

I've a column in a table (varchar) with dates in this format 2013-09-05T10:10:02Z

How do I convert this into datetime format and save it in another column, using an update query?

Answer 1

Updated function inspired a bit from the one the was listed above but wasn't working properly for a lot of my use cases. Should handle any of the following strings below, convert it to datetime with UTC timezone and take into consideration the original offset and maintain the same functional time in UTC timezone.

• 2024-02-09T01:01:05+00:00
• 2024-02-08T23:30:05+01:30
• 2024-02-09T01:01:05-00:00
• 2024-02-09T02:01:05-01:00
• 2024-02-09T11:14:05-10:13
• 2024-02-09T01:01:05z
• 2024-02-09T01:01:05Z

All values = 2024-02-09 01:01 UTC

DELIMITER //
DROP FUNCTION IF EXISTS ISO8601_TO_DATETIME_UTC //
CREATE FUNCTION ISO8601_TO_DATETIME_UTC(iso_datetime VARCHAR(255))
RETURNS DATETIME DETERMINISTIC
BEGIN
    DECLARE datetime_part VARCHAR(255);
    DECLARE offset_seconds INT;
    DECLARE operator CHAR(6);
    DECLARE iso_date_part_format VARCHAR(255) DEFAULT '%Y-%m-%dT%H:%i:%s';
    
    SET iso_date_part_format = '%Y-%m-%dT%H:%i:%s';
    SET operator = SUBSTRING(iso_datetime, LENGTH(iso_datetime) - 5, 7);
    
    #Check for and handle z or Z offset
    IF (LOWER(RIGHT(iso_datetime ,1)) = 'z') THEN
        RETURN (
            STR_TO_DATE(
                TRIM(
                    TRAILING 'z' FROM TRIM(
                        TRAILING 'Z' FROM iso_datetime
                    )
                ), 
                iso_date_part_format
            )
        );
    ELSEIF ( operator = '-00:00' OR  operator = '+00:00') THEN
        RETURN STR_TO_DATE(
            SUBSTRING(iso_datetime, 1, LENGTH(iso_datetime) - 6), 
            iso_date_part_format
        );
    END IF;
    SET datetime_part = SUBSTRING(iso_datetime, 1, LENGTH(iso_datetime) - 6);
    
    #Handle manual +00:00 or -00:00 with approriate offset based on value elsewhere assume no offset
    IF  SUBSTRING(LOWER(RIGHT(iso_datetime, 6)), 1, 1) = '+' OR SUBSTRING(LOWER(RIGHT(iso_datetime, 6)), 1, 1) = '-'  THEN
        #Get offset as seconds
        SET offset_seconds = (SUBSTRING(iso_datetime, -5, 2) * 60 * 60) + (SUBSTRING(iso_datetime, -2, 2) * 60) ;
        #If subtracting multiply by negative one
        IF (SUBSTRING(LOWER(RIGHT(iso_datetime, 6)), 1, 1) = '-') THEN
            SET offset_seconds = offset_seconds * -1;
        END IF;
    ELSE
        SET datetime_part = iso_datetime;
        SET offset_seconds = 0;
    END IF;
    RETURN FROM_UNIXTIME(
    (
        CAST(UNIX_TIMESTAMP(
                     STR_TO_DATE(datetime_part, iso_date_part_format)
             ) as double) +
        offset_seconds
        )
    );
END//

DELIMITER ;

WITH
test_cases as (
SELECT '2024-02-09T01:01:05+00:00' as iso_datetime
UNION ALL
SELECT '2024-02-08T23:30:05+01:30' as iso_datetime
UNION ALL
SELECT '2024-02-09T02:01:05-01:00' as iso_datetime
UNION ALL
SELECT '2024-02-09T11:14:05-10:13' as iso_datetime
UNION ALL
SELECT '2024-02-09T01:01:05z' as iso_datetime
UNION ALL
SELECT '2024-02-09T01:01:05Z' as iso_datetime
)
SELECT iso_datetime, ISO8601_TO_DATETIME_UTC(iso_datetime) FROM test_cases;

Answer 2

This isn't precisely the format you asked, but to properly handle time zones (e.g. '2023-06-06T13:00:00+07:00'), in MariaDB use this function:

DELIMITER //

DROP FUNCTION IF EXISTS ISO2UTC;

CREATE FUNCTION ISO2UTC(iso_datetime VARCHAR(255))
RETURNS DATETIME DETERMINISTIC
BEGIN
  DECLARE datetime_part VARCHAR(255);
  DECLARE offset_str VARCHAR(5);
  DECLARE offset_seconds INT;
  DECLARE unix_timestamp_utc0 INT;
  DECLARE unix_timestamp_utc INT;
  DECLARE datetime_utc DATETIME;

  SET datetime_part = SUBSTRING_INDEX(iso_datetime, '+', 1);
  SET offset_str = SUBSTRING_INDEX(iso_datetime, '+', -1);
  SET offset_seconds = TIME_TO_SEC(CONCAT(offset_str, ':00'));
  SET unix_timestamp_utc0 = UNIX_TIMESTAMP(STR_TO_DATE(datetime_part, '%Y-%m-%dT%H:%i:%s'));
  SET unix_timestamp_utc = unix_timestamp_utc0 - offset_seconds;
  SET datetime_utc = FROM_UNIXTIME(unix_timestamp_utc);

  RETURN datetime_utc;
END//

DELIMITER ;

Then SELECT ISO2UTC('2023-06-06T13:00:00+07:00'); will return

2023-06-06 06:00:00

, as desired.

Answer 3

I tried using the cast method from above but would get the truncated error as describe in the comments.

You can also use CAST('2013-09-05T10:10:02Z' AS DATETIME) which does not require a format definition as in STR_TO_DATE().

I would consistently get: Error: Truncated incorrect datetime value: '2011-10-02T23:25:42Z'

I fixed it by casting the value to an @ variable before using it in my query. Here is an example in a Stored Procedure:

CREATE PROCEDURE `new_procedure`(IN p_date VARCHAR(50), p_text VARCHAR(500))
BEGIN

SET @datestring = CAST(p_date AS DATETIME);

-- used for debugging
SELECT @datestring, p_text;

INSERT INTO testtable(timestamp, text) VALUES(@datestring, p_text);

END

Answer 4

If you want to take care of the timezone just use this query, and use the mysql timezone

mysql> select CONVERT_TZ("2013-09-05T10:10:02Z", "+00:00", @@session.time_zone);

+-------------------------------------------------------------------+
| CONVERT_TZ("2013-09-05T10:10:02Z", "+00:00", @@session.time_zone) |
+-------------------------------------------------------------------+
| 2013-09-05 12:10:02                                               |
+-------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

or any other timezone

mysql> select CONVERT_TZ("2013-09-05T10:10:02Z", "+00:00", "+03:00");
+--------------------------------------------------------+
| CONVERT_TZ("2013-09-05T10:10:02Z", "+00:00", "+03:00") |
+--------------------------------------------------------+
| 2013-09-05 13:10:02                                    |
+--------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Answer 5

You can also use CAST('2013-09-05T10:10:02Z' AS DATETIME) which does not require a format definition as in STR_TO_DATE().

Answer 6

You can use the STR_TO_DATE function:

UPDATE table1 SET col2 = STR_TO_DATE(col1,'%Y-%m-%dT%TZ')

Example:

mysql> select STR_TO_DATE('2013-09-05T10:10:02Z','%Y-%m-%dT%TZ');
+----------------------------------------------------+
| STR_TO_DATE('2013-09-05T10:10:02Z','%Y-%m-%dT%TZ') |
+----------------------------------------------------+
| 2013-09-05 10:10:02                                |
+----------------------------------------------------+
1 row in set (0.00 sec)