Understand microsecond precision. This concept page explains how MariaDB stores and handles fractional seconds in time data types.
The TIME, DATETIME, and TIMESTAMP types, along with the temporal functions, CAST and dynamic columns, support microseconds. The datetime precision of a column can be specified when creating the table with CREATE TABLE, for example:
Generally, the precision can be specified for any TIME, DATETIME, or TIMESTAMP column, in parentheses, after the type name. The datetime precision specifies number of digits after the decimal dot and can be any integer number from 0 to 6. If no precision is specified it is assumed to be 0, for backward compatibility reasons.
A datetime precision can be specified wherever a type name is used. For example:
when declaring arguments of stored routines;
when specifying a return type of a stored function;
when declaring variables;
in a CAST function.
%f is used as the formatting option for microseconds in the , and functions, for example:
When comparing anything to a temporal value (DATETIME, TIME, , or TIMESTAMP), both values are compared as temporal values, not as strings.
The has a new column DATETIME_PRECISION
, , , ,
and preserve microseconds of the argument. These functions will return a number if the result non-zero datetime precision and an otherwise (for backward compatibility).
Current versions of this patch fix a bug in the following optimization: In certain queries with DISTINCT MariaDB can ignore this clause if it can prove that all result rows are unique anyway, for example, when a primary key is compared with a constant. Sometimes this optimization was applied incorrectly, though — for example, when comparing a string with a date constant. This is now fixed.
DATE_ADD() and DATE_SUB() functions can now take a TIME expression as an argument (not just DATETIME as before).
The event_time field in the table and the start_time, query_time, and lock_time fields in the table now store values with microsecond precision.
The old syntax TIMESTAMP(N), where N is the display width, is no longer supported.
Note: When you convert a temporal value to a value with a smaller precision, it will be truncated, not rounded. This is done to guarantee that the date part is not changed. For example:
This page is licensed: CC BY-SA / Gnu FDL
CREATE TABLE example(
col_microsec DATETIME(6),
col_millisec TIME(3)
);When a DATETIME value is compared to a TIME value, the latter is treated as a full datetime with a zero date part, similar to comparing DATE to a DATETIME, or to comparing DECIMAL numbers.
Earlier versions of MariaDB used to compare only the time part of both operands in such a case.
In MariaDB, an extra column TIME_MS has been added to the INFORMATION_SCHEMA.PROCESSLIST table, as well as to the output of SHOW FULL PROCESSLIST.
CREATE FUNCTION example(x DATETIME(5)) RETURNS TIME(4)
BEGIN
DECLARE y TIMESTAMP(6);
RETURN CAST(x AS time(2));
END;SELECT STR_TO_DATE('20200809 020917076','%Y%m%d %H%i%s%f');
+-----------------------------------------------------+
| STR_TO_DATE('20200809 020917076','%Y%m%d %H%i%s%f') |
+-----------------------------------------------------+
| 2020-08-09 02:09:17.076000 |
+-----------------------------------------------------+SELECT CURTIME(4);
--> 10:11:12.3456SELECT TIME_TO_SEC('10:10:10.12345');
--> 36610.12345SELECT TIME('10:10:10') + INTERVAL 100 MICROSECOND;
--> 10:10:10.000100SELECT CAST('2009-12-31 23:59:59.998877' AS DATETIME(3));
-> 2009-12-31 23:59:59.998