DATETIME and TIMESTAMP Length/Values Error

问题:

I was using int type for storing date/time. For the convenience of getting the data in specific range from MySQL, I tried changing it to TIMESTAMP/DATETIME but it is giving the error as in the attached image in both the cases. The format for datatype TIMESTAMP/DATETIME is YYYY-MM-DD HH:MM:SS which is 19 character long.

I could not get a proper tutorial/article as I could start from beginning.

Error Image

回答1:

When defining a DATETIME or TIMESTAMP field, there's no need to specify the length.

This is what the error message refers to:

A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision

MySQL permits fractional seconds for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision. To define a column that includes a fractional seconds part, use the syntax type_name(fsp), where type_name is TIME, DATETIME, or TIMESTAMP, and fsp is the fractional seconds precision. For example:

CREATE TABLE t1 (t TIME(3), dt DATETIME(6));

Abstract from CREATE TABLE Syntax:

| TIME[(fsp)]
| TIMESTAMP[(fsp)]
| DATETIME[(fsp)]

Documentation:

• The DATE, DATETIME, and TIMESTAMP Types
• Date and Time Type Overview