I was using int
type for storing datetime type data. 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 to get knowledge about this error/issue.
Error Image
You can use str_to_date to convert a date string to MySQL's internal date format for inserting.
Timestamps are also lighter on the database and indexed faster. The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in YYYY-MM-DD HH:MM:SS format.
Range − Datetime data type supports a date along with time in the range between 1000-01-01 00:00:00 and 9999-12-31 23:59:59. But timestamp data type supports a date along with time in the range between '1970-01-01 00:00:01' to '2038-01-19 08:44:07'.
The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in ' YYYY-MM-DD hh:mm:ss ' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59' . The TIMESTAMP data type is used for values that contain both date and time parts.
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:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With