MySQL Time with subsecond precision
Remarks[edit | edit source]
You need to be at MySQL version 5.6.4 or later to declare columns with fractional-second time datatypes.
DATETIME(3) will give you millisecond resolution in your timestamps, and
TIMESTAMP(6) will give you microsecond resolution on a *nix-style timestamp.
NOW(3) will give you the present time from your MySQL server's operating system with millisecond precision.
(Notice that MySQL internal fractional arithmetic, like * 0.001, is always handled as IEEE754 double precision floating point, so it's unlikely you'll lose precision before the Sun becomes a white dwarf star.)
Get the current time with millisecond precision[edit | edit source]
does the trick.
time_t data type, and show the number of milliseconds since
1970-01-01 00:00:00 UTC.
ROUND(UNIX_TIMESTAMP(NOW(3)) * 1000.0, 0)
If you have
SELECT ROUND(UNIX_TIMESTAMP(column) * 1000.0, 0)
If your column contains
Create a table with columns to store sub-second time.[edit | edit source]
CREATE TABLE times ( dt DATETIME(3), ts TIMESTAMP(3) );
makes a table with millisecond-precision date / time fields.
INSERT INTO times VALUES (NOW(3), NOW(3));
inserts a row containing
NOW() values with millisecond precision into the table.
INSERT INTO times VALUES ('2015-01-01 16:34:00.123','2015-01-01 16:34:00.128');
inserts specific millisecond precision values.
Notice that you must use
NOW(3) rather than
NOW() if you use that function to insert high-precision time values.
Convert a millisecond-precision date / time value to text.[edit | edit source]
%f is the fractional precision format specifier for the DATE_FORMAT() function.
SELECT DATE_FORMAT(NOW(3), '%Y-%m-%d %H:%i:%s.%f')
displays a value like
2016-11-19 09:52:53.248000 with fractional microseconds. Because we used
NOW(3), the final three digits in the fraction are 0.
1478960868932, you can convert that to a MySQL fractional time value like this:
FROM_UNIXTIME(1478960868932 * 0.001)
INSERT INTO table (col) VALUES (FROM_UNIXTIME(1478960868932 * 0.001))
(Obviously, you'll want to insert other columns.)