MySQL插入ISO8601日期时间格式 [英] MySQL insert ISO8601 Datetime format

问题描述

我在将ISO8601 Datetime格式插入MySQL数据库时遇到麻烦.

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

我想将ISO格式(即 yyyymmddThhmmss + | -hhmm )插入数据库表的DATETIME列.

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:

操作失败:将SQL脚本应用于 数据库.执行中:UPDATE db.orders SET date ='20080915T155300 + 0500'在id ='1';

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';

错误1292:1292:错误的日期时间值:'20080915T155300 + 0500' 第1行SQL语句的'日期'列:UPDATE db.orders SET date ='20080915T155300 + 0500'在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'

有什么方法可以将这种格式的日期时间保存到MySQL中?

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

推荐答案

MySQL对时区提供了相当神秘的支持.也许这就是您想要的:

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

这篇关于MySQL插入ISO8601日期时间格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！