How many digits should be in a string or number to be specified as a date value by MySQL?

Although a year is treated as a 4-digit value, MySQL requires at least 8 digits in a string or number to specify it as a date value. In this case, if we also want to store microseconds, the value can be up to 20 digits.

mysql> Select TIMESTAMP('20171022040536.100000');
+-----------------------------------+
| TIMESTAMP('20171022040536100000') |
+-----------------------------------+
| 2017-10-22 04:05:36.100000        |
+-----------------------------------+
1 row in set, 1 warning (0.00 sec)

The above query takes a 20-digit numeric string as the TIMESTAMP value. The last 6 digits represent microseconds.

mysql> Select TIMESTAMP(20171022);
+---------------------+
| TIMESTAMP(20171022) |
+---------------------+
| 2017-10-22 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

The above query takes an 8-digit string as the TIMESTAMP value.

mysql> Select TIMESTAMP(201710);
+-------------------+
| TIMESTAMP(201710) |
+-------------------+
| NULL              |
+-------------------+
1 row in set, 1 warning (0.00 sec)

The above query returns NULL because the number has less than 8 digits.

In contrast, when treating a year as a 2-digit number, at least 6 digits are required in the string or number MySQL specifies as a date value. In this case, if we also want to store microseconds, the value can be up to 18 digits.

mysql> Select TIMESTAMP(171022);
+---------------------+
| TIMESTAMP(171022)   |
+---------------------+
| 2017-10-22 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

The above query takes a 6-digit string as the TIMESTAMP value.

mysql> Select TIMESTAMP('171022040536.200000');
+----------------------------------+
| TIMESTAMP('171022040536.200000') |
+----------------------------------+
| 2017-10-22 04:05:36.200000       |
+----------------------------------+
1 row in set (0.00 sec)

The above query takes an 18-digit numeric string as the TIMESTAMP value. The last 6 digits after the dot (.) represent microseconds.

mysql> Select TIMESTAMP(1710);
+-----------------+
| TIMESTAMP(1710) |
+-----------------+
| NULL            |
+-----------------+
1 row in set, 1 warning (0.00 sec)

The above query returns NULL because the number of digits is less than 6.

The above is the detailed content of How many digits should be in a string or number to be specified as a date value by MySQL?. For more information, please follow other related articles on the PHP Chinese website!