https://dev.mysql.com/doc/refman/8.0/en/fractional-seconds.html

MySQL supports only milliseconds. Do we need to send nanoseconds?

MySQL supports only milliseconds.

You mean microseconds?

At least, we need to send 100 nanoseconds (7 digits) precision.
because the DATETIME converter can handle them.

Some examples:

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

mysql> SELECT CONVERT('2020-11-27 10:00:00.1234564', DATETIME(6));
+-----------------------------------------------------+
| CONVERT('2020-11-27 10:00:00.1234564', DATETIME(6)) |
+-----------------------------------------------------+
| 2020-11-27 10:00:00.123456                          |
+-----------------------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT CONVERT('2020-11-27 10:00:00.1234565', DATETIME(6));
+-----------------------------------------------------+
| CONVERT('2020-11-27 10:00:00.1234565', DATETIME(6)) |
+-----------------------------------------------------+
| 2020-11-27 10:00:00.123457                          |
+-----------------------------------------------------+
1 row in set (0.01 sec)

mysql> SET @@sql_mode = sys.list_add(@@sql_mode, 'TIME_TRUNCATE_FRACTIONAL');
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT CONVERT('2020-11-27 10:00:00.1234564', DATETIME(6));
+-----------------------------------------------------+
| CONVERT('2020-11-27 10:00:00.1234564', DATETIME(6)) |
+-----------------------------------------------------+
| 2020-11-27 10:00:00.123456                          |
+-----------------------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT CONVERT('2020-11-27 10:00:00.1234565', DATETIME(6));
+-----------------------------------------------------+
| CONVERT('2020-11-27 10:00:00.1234565', DATETIME(6)) |
+-----------------------------------------------------+
| 2020-11-27 10:00:00.123456                          |
+-----------------------------------------------------+
1 row in set (0.01 sec)

As you say, currently MySQL only supports up to microseconds.
However, there is no guarantee that it will not support nanoseconds in the future.
So, I think that we should send all digits we know.

I think you are right, but still feel it is a bit too much.

#1121 is really a bug. Round off 1.4449995 to 1.45 doesn't make sense at all.
Compared to it, behavior consistency between round off and round down is very minor. Should we send another 3 digits only for this minor consistency?

Let's check another connectors behavior.

It seems Connector/J just nano/1000 when store it in binary protocol.

https://github.com/mysql/mysql-connector-j/blob/d64b664fa93e81296a377de031b8123a67e6def2/src/main/core-impl/java/com/mysql/cj/ServerPreparedQueryBindValue.java#L325
https://github.com/mysql/mysql-connector-j/blob/d64b664fa93e81296a377de031b8123a67e6def2/src/main/core-impl/java/com/mysql/cj/ServerPreparedQueryBindValue.java#L330

But they round or truncate nanoseconds based on TIME_TRUNCATE_FRACTIONAL sql_mode.

https://github.com/mysql/mysql-connector-j/blob/d64b664fa93e81296a377de031b8123a67e6def2/src/main/core-api/java/com/mysql/cj/util/TimeUtil.java#L183-L186

OK. I'll check another connectors behavior, too.

There is the issue about this, so we should discuss in #1121.
I'll convert this pull request to draft.
Let's continue discussion in #1121.

I think this pull request is good for v1.6. @shogo82148 would you merge master branch to run CI?

@methane I did!

Is it safe to trim trailing 0s?

Maybe, it is.

It looks that starting MySQL server fails on macOS. it is not due to my pull request.

https://github.com/go-sql-driver/mysql/pull/1172/checks?check_run_id=1806566232

To have launchd start mysql now and restart at login:
  brew services start mysql
Or, if you don't want/need a background service you can just run:
  mysql.server start
Starting MySQL
/usr/local/Cellar/mysql/8.0.23/bin/mysqld_safe: line 653: /usr/local/var/mysql/Mac-1612182949676.local.err: No such file or directory
Logging to '/usr/local/var/mysql/Mac-1612182949676.local.err'.
/usr/local/Cellar/mysql/8.0.23/bin/mysqld_safe: line 144: /usr/local/var/mysql/Mac-1612182949676.local.err: No such file or directory
/usr/local/Cellar/mysql/8.0.23/bin/mysqld_safe: line 199: /usr/local/var/mysql/Mac-1612182949676.local.err: No such file or directory
/usr/local/Cellar/mysql/8.0.23/bin/mysqld_safe: line 916: /usr/local/var/mysql/Mac-1612182949676.local.err: No such file or directory
/usr/local/Cellar/mysql/8.0.23/bin/mysqld_safe: line 144: /usr/local/var/mysql/Mac-1612182949676.local.err: No such file or directory
. ERROR! The server quit without updating PID file (/usr/local/var/mysql/Mac-1612182949676.local.pid).
Error: Process completed with exit code 1.

Thank you for your review!