Search code examples
mysqlsqliso

MySQL insert ISO8601 Datetime format

I am having trouble with inserting ISO8601 Datetime format into my MySQL database.

I would like to insert ISO format (i.e. yyyymmddThhmmss+|-hhmm) into my database table, DATETIME column.

When I try to insert I got problem with:

Operation failed: There was an error while applying the SQL script to the database. Executing: UPDATE db.orders SET date='20080915T155300+0500' WHERE id='1';

ERROR 1292: 1292: Incorrect datetime value: '20080915T155300+0500' for column 'date' at row 1 SQL Statement: UPDATE db.orders SET date='20080915T155300+0500' WHERE id='1'

Is there any way that I can save datetimes with this format into MySQL?

Solution

  • MySQL has rather arcane support for time zones. Perhaps this does what you want:

    select convert_tz(str_to_date(left(val, 15), '%Y%m%dT%H%i%s'), '+00:00', insert(right(val, 5), 4, 0, ':'))
from (select '20080915T155300+0500' as val) x