插入 MySQL 时出现日期时间错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17870222/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
DateTime error while insert to MySQL
提问by user2620720
I come up with an error while inserting the following data into MySQL. How can I fix it?
将以下数据插入 MySQL 时出现错误。我该如何解决？
ERROR 1292: Incorrect datetime value: '17/07/2013 18:33:55' for column 'TimeStamp' at row 1
错误 1292：日期时间值不正确：第 1 行的“TimeStamp”列的“17/07/2013 18:33:55”
SQL Statement:
SQL语句：
INSERT INTO `wngtest`.`sitereading` (`idSiteReading`, `TimeStamp`, `SiteLocation`, `Flow`, `Temperature1`, `Temperature2`) VALUES ('1', '17/07/2013 18:33:55', 'WNGSite1', '13.1', '81', '45')
ERROR 1292: Incorrect datetime value: '17/07/2013 18:18:53' for column 'TimeStamp' at row 1
错误 1292：日期时间值不正确：第 1 行的“TimeStamp”列的“17/07/2013 18:18:53”
SQL Statement:
SQL语句：
INSERT INTO `wngtest`.`sitereading` (`idSiteReading`, `TimeStamp`, `SiteLocation`, `Flow`, `Temperature1`, `Temperature2`) VALUES ('2', '17/07/2013 18:18:53', 'WNGSite1', '13', '80', '45')
ERROR 1292: Incorrect datetime value: '17/07/2013 18:03:54' for column 'TimeStamp' at row 1
错误 1292：日期时间值不正确：第 1 行的“TimeStamp”列的“17/07/2013 18:03:54”
SQL Statement:
SQL语句：
INSERT INTO `wngtest`.`sitereading` (`idSiteReading`, `TimeStamp`, `SiteLocation`, `Flow`, `Temperature1`, `Temperature2`) VALUES ('3', '17/07/2013 18:03:54', 'WNGSite1', '12.7', '80', '45')
ERROR 1292: Incorrect datetime value: '17/07/2013 17:48:54' for column 'TimeStamp' at row 1
错误 1292：日期时间值不正确：第 1 行的“TimeStamp”列的“17/07/2013 17:48:54”
SQL Statement:
SQL语句：
INSERT INTO `wngtest`.`sitereading` (`idSiteReading`, `TimeStamp`, `SiteLocation`, `Flow`, `Temperature1`, `Temperature2`) VALUES ('4', '17/07/2013 17:48:54', 'WNGSite1', '12.7', '80', '45')
ERROR 1292: Incorrect datetime value: '17/07/2013 17:33:55' for column 'TimeStamp' at row 1
错误 1292：日期时间值不正确：第 1 行的“TimeStamp”列的“17/07/2013 17:33:55”
SQL Statement:
SQL语句：
INSERT INTO `wngtest`.`sitereading` (`idSiteReading`, `TimeStamp`, `SiteLocation`, `Flow`, `Temperature1`, `Temperature2`) VALUES ('5', '17/07/2013 17:33:55', 'WNGSite1', '12.8', '80', '45')
ERROR 1292: Incorrect datetime value: '17/07/2013 17:18:55' for column 'TimeStamp' at row 1
错误 1292：日期时间值不正确：第 1 行的“TimeStamp”列的“17/07/2013 17:18:55”
SQL Statement:
SQL语句：
INSERT INTO `wngtest`.`sitereading` (`idSiteReading`, `TimeStamp`, `SiteLocation`, `Flow`, `Temperature1`, `Temperature2`) VALUES ('6', '17/07/2013 17:18:55', 'WNGSite1', '12.9', '80', '45')
回答by fenway
Alternatively, you can automatically re-format your date string to SQL-99 format using STR_TO_DATE():
或者，您可以使用以下命令自动将日期字符串重新格式化为 SQL-99 格式STR_TO_DATE()：
STR_TO_DATE( '17/07/2013 18:33:55', '%d/%m/%Y %H:%i:%s')
So the INSERT statement would be:
所以 INSERT 语句将是：
INSERT INTO wngtest.sitereading
(idSiteReading, TimeStamp, SiteLocation, Flow, Temperature1, Temperature2)
VALUES ('1',
STR_TO_DATE( '17/07/2013 18:33:55', '%d/%m/%Y %H:%i:%s'),
'WNGSite1', '13.1', '81', '45');
回答by Kevin Bowersox
In MySql dates should be inserted in yyyy-mm-ddformat
在 MySql 中日期应该以yyyy-mm-dd格式插入
Try using the format:
尝试使用以下格式：
2013-07-17 17:18:55
2013-07-17 17:18:55
Full Insert Statement:
完整的插入语句：
INSERT INTO wngtest.sitereading
(idSiteReading, TimeStamp, SiteLocation, Flow, Temperature1, Temperature2)
VALUES ('1', '2013-07-17 18:33:55', 'WNGSite1', '13.1', '81', '45');
