Mawg Mawg - 3 years ago 2349
SQL Question

Mysql Invalid datetime format: 1292 Incorrect datetime value

mysql> describe taps;
+-------------+-------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+-------------------+-------+
| tag_id | int(11) | NO | | NULL | |
| time_stamp | timestamp | NO | | CURRENT_TIMESTAMP | |
| device_id | varchar(45) | YES | | NULL | |
| device_type | varchar(45) | YES | | NULL | |
+-------------+-------------+------+-----+-------------------+-------+

mysql> INSERT INTO `taps` (tag_id, time_stamp) VALUES(0, 1451610061);
ERROR 1292 (22007): Incorrect datetime value: '1451610061' for column 'time_stamp' at row 1


WHY?? I have found many similar questions, but not of them seem quite this black and white.

1451610061
is a valid timestamp. I checked it at http://www.unixtimestamp.com/ and it evaluates as expected.

So, why doesn't MySql like it?

Answer Source

The MySQL timestamp format is 2016-02-13 15:48:29 or Y-m-d H:i:s convert your timestamp to that format first, and then MySQL will accept it.

If you insert a new record without defining the timestamp, and then select the row from that table, you will notice that that's the format that it gives to the new default record.

if if you want to convert it directly in your query use:

INSERT INTO `taps` (tag_id, time_stamp) VALUES(0, from_unixtime('1451610061'));

Using this Q&A on StackOverflow as reference. And from_unixtime documentation

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download