How to Achieve Millisecond Precision Timestamps in MySQL?

How to Save Timestamps with Millisecond Precision in MySQL

When dealing with precise timestamps in MySQL, it becomes important to understand the limitations of the timestamp datatype. By default, MySQL stores timestamps with second precision. If you need to store timestamps with millisecond precision, certain considerations come into play.

Upgrading MySQL

Firstly, MySQL version 5.6.4 or later is required to support fractional-second time datatypes. To verify your version, use the query SELECT NOW(3). If an error occurs, you may need to upgrade MySQL.

Declaration of Columns

To create columns with fractional-second time datatypes, use the following syntax:

DATETIME(n)

Where n represents the number of fractional seconds. For example, DATETIME(3) will provide millisecond resolution. Similarly, TIMESTAMP(n) can be used, where n specifies the microsecond resolution on Unix-style timestamps.

Retrieval of Subsecond Time

To retrieve the system time with millisecond precision, use NOW(3):

SELECT NOW(3);

If you have timestamps represented as milliseconds since the Unix epoch (e.g., JavaScript timestamps), convert them using FROM_UNIXTIME(ms * 0.001).

Older MySQL Versions

If upgrading MySQL is not an option, consider using BIGINT or DOUBLE columns to store timestamps as numbers. The conversion to microseconds can still be achieved using FROM_UNIXTIME(col * 0.001). To store the current time in such a column, use UNIX_TIMESTAMP() * 1000.

Conclusion

By understanding the fractional-second time datatypes and conversion techniques, you can effectively store and retrieve timestamps with millisecond precision in MySQL. Ensuring that you have the appropriate MySQL version and implementing the correct column declaration syntax are key to working with subsecond timestamps.

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