mysql time format problem

Question

The data type is datetime, why are there so many 000000 at the end? How to deal with it?
The default added time is '2017-06-09 10:07:33'. After adding data in this way, a lot of 0s will be added by default, which is crazy

Answer 1

show variables like 'datetime_format';

This is the format you want to set the datetime type, and you probably have milliseconds behind it, so just change it to the above.

How to change it?
Add it under [mysqld] in the mysql configuration file my.cnf or my.ini

datetime_format=%Y-%m-%d %H:%i:%s

After saving the changes, remember to restart the mysql service!

Answer 2

Time accuracy issue, storage time accuracy is second level, millisecond level, microsecond level,

DATETIME range: 1000-01-01 00:00:00' to '9999-12-31 23:59:59', in seconds

Your time representation is at the microsecond level, so there is no time accuracy after seconds, so they are all 0

How to solve it
1. Don’t worry about it, just format it when outputting, you can do it any time you want
2. Use other data types, I recommend int

Answer 3

Your time should be taken in subtly and then converted to date format.
If you don’t want the later format, just use the 11-digit timestamp.
Look at the timestamp you got
The new precision of the time type in mysql 5.6

Answer 4

The picture simply changes the data type timestamp This way, 0 will not appear by default, and the length is set to 0

Answer 5

@nicezhu Which version of the database is used? The one I use is always accurate to the second level