MySQL Fractional Seconds Precision in Time Data Types

This article explains how MySQL handles fractional seconds precision for temporal data types, including TIME, DATETIME, and TIMESTAMP. You will learn how to define fractional precision, how MySQL manages rounding during data insertion, and the storage implications of using high-precision time values in your database tables.

Supported Data Types and Syntax

MySQL supports fractional seconds for the TIME, DATETIME, and TIMESTAMP data types. To enable fractional seconds, you must specify the fractional seconds precision (FSP) in parentheses when defining the column.

The syntax for declaration is:

type_name(fsp)

The fsp value represents the number of digits after the decimal point and must be an integer from 0 to 6. * A precision of 0 means no fractional seconds (this is the default behavior if no FSP is specified). * A precision of 3 represents milliseconds. * A precision of 6 represents microseconds, which is the maximum supported precision in MySQL.

For example, to create a table with varying levels of fractional precision, you would write:

CREATE TABLE precision_example (
    event_time TIME(3),
    created_at TIMESTAMP(6),
    updated_at DATETIME(0)
);

How MySQL Handles Rounding

When you insert a time value that contains more fractional second digits than the column’s defined precision, MySQL rounds the value rather than truncating it.

For instance, if you insert the value '2023-10-27 12:30:45.5678' into a DATETIME(2) column, MySQL rounds the fractional part to two decimal places. The stored value becomes '2023-10-27 12:30:45.57'.

If rounding causes a value to overflow into the next unit of time (such as rounding .999 to 1.000), MySQL propagates the carryover to the seconds, minutes, hours, or even the date. For example, inserting '2023-12-31 23:59:59.999' into a DATETIME(0) column will result in the value being rounded up to '2024-01-01 00:00:00'.

Storage Requirements

Adding fractional seconds precision increases the storage footprint of your temporal columns. MySQL allocates storage for the fractional seconds part in addition to the base storage required for the temporal data type.

The extra storage requirements for fractional seconds are allocated as follows:

To calculate the total storage of a column, add this fractional storage to the base storage of the data type. For example, in modern MySQL versions, a standard DATETIME column requires a base of 5 bytes of storage. If you define it as DATETIME(6), it will require an additional 3 bytes, resulting in a total of 8 bytes of storage per row.