CODEWITHSUNDEEP
mysqldatetime
Mawg
Mawg

Reputation: 40140

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?

Upvotes: 2

Views: 6139

Answers (2)

Maizied Hasan Majumder
Maizied Hasan Majumder

Reputation: 1053

Try this ->

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

Upvotes: 1

Adam Copley
Adam Copley

Reputation: 1495

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

Upvotes: 5

Related Questions