Mysql Invalid datetime format: 1292 Incorrect datetime value

Question

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 1

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

Answer 2

Try this ->

$item->time_stamp = date('Y-m-d H:i:s', strtotime($request->time_stamp));