How many digits should be there in string or number so that it can be specified as a date value by MySQL?

While considering the year as 4-digit value, minimum of 8 digits in a string or number is required for MySQL to specify it as a date value. In this case, if we also want to store microseconds then the value can be up to a maximum of 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 query above is taking 20 digits string for TIMESTAMP value. Last 6 digits are for microseconds.

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

The query above is taking 8 digits string for TIMESTAMP value.

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

The query above returns NULL because the numbers of a digit are less than 8.

In contrast, while considering the year as 2-digit value, minimum of 6 digits in a string or number is required for MySQL to specify it as a date value. In this case, if we also want to store microseconds then the value can be up to a maximum of 18 digits.

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

The query above is taking 6 digits string for 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 query above is taking 18 digits string for TIMESTAMP value. Last 6 digits after dot (.) are for microseconds.

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

The query above returns NULL because the numbers of digit are less than 6.

Sreemaha

Published on 19-Feb-2018 10:56:01

Previous Page Print Page