How to Store Millisecond Precision Timestamps in MySQL?

How to Save Millisecond Precision Timestamps in MySQL

In MySQL, ensuring accurate timestamp precision is crucial for applications that demand high temporal resolution. To achieve millisecond precision for timestamps, consider the following steps:

Declare Columns with Fractional-Second Time Datatypes

To declare columns with fractional-second time datatypes, ensure you're using MySQL version 5.6.4 or later. Use NOW(3) to check your version, and if you receive an error, upgrade to a supported version.

Select an Appropriate Time Datatype

For millisecond resolution, use DATETIME(3). For microsecond resolution, use TIMESTAMP(6).

Example Declaration

CREATE TABLE IF NOT EXISTS probability` (<br>id int(11) NOT NULL AUTO_INCREMENT,<br>segment_id int(11) NOT NULL,<br>probability float NOT NULL,<br>measured_at DATETIME(3) NOT NULL,<br>provider_id int(11) NOT NULL,<br>PRIMARY KEY (id)<br>) ;<br>

`

Convert Milliseconds to DATETIME(3)

To convert a number of milliseconds since the Unix epoch to a DATETIME(3) value, use the following expression:

`

FROM_UNIXTIME(ms * 0.001)<br>

`

Consider Upgrading for Subsecond Time Precision

If you're using an older MySQL version and need subsecond time precision, upgrading is recommended. Using workarounds may introduce complexities. However, if upgrading isn't feasible, consider storing timestamps as numbers in BIGINT or DOUBLE columns, using the conversion expression:

`

FROM_UNIXTIME(col * 0.001)<br>

`

By following these guidelines, you can effectively store and retrieve timestamps with millisecond precision in MySQL, ensuring temporal accuracy for your applications.

The above is the detailed content of How to Store Millisecond Precision Timestamps in MySQL?. For more information, please follow other related articles on the PHP Chinese website!