MySQL insert ISO8601 Datetime format

1
votes

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?

1
mysqlsqliso
"Is there any way that I can save datetimes with this format into MySQL?" No there isn't a native way.. think you should have the datetime column and time zone column... that way you can calculate the datetime with the timezone on the fly with CONVERT_TZ when selecting – Raymond Nijland

1 Answers

0
votes

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