How Can I Store and Retrieve Millisecond-Precision Timestamps in MySQL?

MySQL Timestamps with Millisecond Precision

The need to store timestamps with higher precision often arises in applications. MySQL supports fractional-second time datatypes, providing a solution to this.

To declare a column with millisecond precision, use DATETIME(3) or TIMESTAMP(6) for microsecond precision. For MySQL versions prior to 5.6.4, it is recommended to upgrade to utilize these features.

To obtain the current time with millisecond precision, use NOW(3). For timestamps represented as milliseconds since the Unix epoch, use FROM_UNIXTIME(ms * 0.001) to convert them to DATETIME(3) format.

If using an older MySQL version and requiring subsecond precision, consider using BIGINT or DOUBLE columns to store timestamps as numbers. FROM_UNIXTIME(col * 0.001) can still be used to convert them. To store the current time in such a column, use UNIX_TIMESTAMP() * 1000.

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