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:
CREATE TABLE example( col_microsec DATETIME(6), col_millisec TIME(3) );
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:
CAST
function:create function example(x datetime(5)) returns time(4) begin declare y timestamp(6); return cast(x as time(2)); end;
DATETIME
, TIME
, DATE
, or TIMESTAMP
), both values are compared as temporal values, not as strings. DATETIME_PRECISION
SELECT CURTIME(4); --> 10:11:12.3456
SELECT TIME_TO_SEC('10:10:10.12345'); --> 36610.12345
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).SELECT TIME('10:10:10') + INTERVAL 100 MICROSECOND; --> 10:10:10.000100
event_time
field in the mysql.general_log table and the start_time
, query_time
, and lock_time
fields in the mysql.slow_log table now store values with microsecond precision. BETWEEN
operator and one of the operands is NULL
. TIMESTAMP(N)
, where N
is the display width, is no longer supported. It was deprecated in MySQL 4.1.0 (released on 2003-04-03). 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. TIME_MS
has been added to the INFORMATION_SCHEMA.PROCESSLIST
table, as well as to the output of SHOW FULL PROCESSLIST
. 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:
SELECT CAST('2009-12-31 23:59:59.998877' as DATETIME(3)); -> 2009-12-31 23:59:59.998
MySQL 5.6 introduced microseconds using a slightly different implementation to MariaDB 5.3. Since MariaDB 10.1, MariaDB has defaulted to the MySQL format, by means of the --mysql56-temporal-format variable. The MySQL version requires slightly more storage but has some advantages in permitting the eventual support of negative dates, and in replication.
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/microseconds-in-mariadb/