Why are fractional seconds in date/time values truncated in MySql/MariaDB?

Question

I have a QIE database connection to a MySql/MariaDB database and I need to store date/time values and preserve the fractional seconds. When I send something like this "2020-05-21 01:41:30.059" the fractional seconds (.059) appear to be truncated.

Is there a way to preserve the fractional seconds?

Answer 1

Fractional second support in MySql and MariaDB is dependent on the fields definition.  If you have control over the DDL to create the tables then check to make sure the field is defined so that it supports fractional seconds.  You will first need to adjust your CREATE TABLE statements to provide the precision needed.

For example:  

       "myDateField DATETIME" 

    and 

        "myDateField DATETIME(0)" 

    do not support fractional seconds.

    While 

        "myDateField DATETIME(6)" 

    does supports fractional seconds up to micro seconds.

See:

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

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

    https://mariadb.com/kb/en/microseconds-in-mariadb/

NOTE:

You may also need to make sure you send fractional seconds by checking your connection URL/Strings for the appropriate use of this connection property/parameter: sendFractionalSeconds (Mysql)/useFractionalSeconds (MariaDB)

See:

    https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html

    https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-configuration-properties.html

    https://mariadb.com/kb/en/about-mariadb-connector-j/