MySQL Bugs: #108395: Fractions of DATETIME columns are truncated/rounded rather than stored

Bug #108395	Fractions of DATETIME columns are truncated/rounded rather than stored
Submitted:	6 Sep 2022 8:51	Modified:	21 Sep 2022 16:40
Reporter:	Foo Bar	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S4 (Feature request)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	datetime, fractional seconds

Description:
The documentation of the DATETIME type (https://dev.mysql.com/doc/refman/8.0/en/datetime.html) explains that the fractions of a second is still stored, even when the precision of the fractions is too low for the DATETIME value being saved:

> In particular, any fractional part in a value inserted into a DATETIME or TIMESTAMP column is stored rather than discarded.

This suggests that values like `2022-01-01 00:00:00.123456` for a DATETIME(0) column are internally stored as such, but are displayed as `2022-01-01 00:00:00`.

However, the documentation of fractional seconds (https://dev.mysql.com/doc/refman/8.0/en/fractional-seconds.html) describe that the values to insert are rounded/truncated:

> Inserting a TIME, DATE, or TIMESTAMP value with a fractional seconds part into a column of the same type but having fewer fractional digits results in rounding.

This can result in confusions as the value inserted has been rounded/truncated when saved. And even if it is stored internally, the fractions cannot be retrieved anymore with and functions like CAST() or MICROSECOND().

How to repeat:
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.27    |
+-----------+
1 row in set (0.00 sec)

mysql> CREATE TABLE dt_test (someDateTimeColumn DATETIME(0));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO dt_test(someDateTimeColumn) VALUES ('2022-01-01 00:00:00.123456');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT *, CAST(someDateTimeColumn AS DATETIME(6)), MICROSECOND(someDateTimeColumn) FROM dt_test;
+---------------------+-----------------------------------------+---------------------------------+
| someDateTimeColumn  | CAST(someDateTimeColumn AS DATETIME(6)) | MICROSECOND(someDateTimeColumn) |
+---------------------+-----------------------------------------+---------------------------------+
| 2022-01-01 00:00:00 | 2022-01-01 00:00:00.000000              |                               0 |
+---------------------+-----------------------------------------+---------------------------------+
1 row in set (0.00 sec)

Suggested fix:
The documentation of the DATETIME column on (https://dev.mysql.com/doc/refman/8.0/en/datetime.html) (and maybe earlier versions as well) should remove the sentence:

> In particular, any fractional part in a value inserted into a DATETIME or TIMESTAMP column is stored rather than discarded.

and replace it with a sentence like:

> Any fraction seconds part of a DATETIME or TIMESTAMP value will be rounded or truncated (based on the TIME_TRUNCATE_FRACTIONAL setting) to fit into the precision of the DATETIME or TIMESTAMP column.

Hi Mr. Foo Bar,

Thank you for your bug report.

However, this is not a bug.

The fractional part is stored, but you can not retrieve it with a CAST. Try assigning the value from that tuple into the properly defined DATETIME. Do it with 8.0.30, please ....

Not a bug.

I have checked the issue with the current version 8.0.30 again, but the result is the same:

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.30    |
+-----------+
1 row in set (0.00 sec)

mysql> CREATE TABLE dt_test (someDateTimeColumn DATETIME(0), columnWithPrecision DATETIME(6));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO dt_test(someDateTimeColumn, columnWithPrecision) VALUES ('2022-01-01 00:00:00.123456', '2022-01-01 00:00:00.123456');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT *, MICROSECOND(someDateTimeColumn), MICROSECOND(columnWithPrecision) FROM dt_test;
+---------------------+----------------------------+---------------------------------+----------------------------------+
| someDateTimeColumn  | columnWithPrecision        | MICROSECOND(someDateTimeColumn) | MICROSECOND(columnWithPrecision) |
+---------------------+----------------------------+---------------------------------+----------------------------------+
| 2022-01-01 00:00:00 | 2022-01-01 00:00:00.123456 |                               0 |                           123456 |
+---------------------+----------------------------+---------------------------------+----------------------------------+
1 row in set (0.00 sec)

(result also available via https://dbfiddle.uk/ZTziJPRc)

I also tried saving the value from the DATETIME(0) column to a variable and SELECT its value, but it doesn't show the fraction part as well. Running an UPDATE query like "UPDATE dt_test SET columnWithPrecision=someDateTimeColumn;" also does not reveal the fraction part of the value in the DATETIME(0) column.

The documentation on https://dev.mysql.com/doc/refman/8.0/en/datetime.html say that any fraction part is stored rather than discarded. If that is the case, where is it stored (and how can I retrieve it)? That's why I propose to rewrite that sentence that the value is rounded or truncated to fit the precision of the DATETIME (and TIMESTAMP) column.