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
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:
create function example(x datetime(5)) returns time(4) begin declare y timestamp(6); return cast(x as time(2)); end;
TIMESTAMP), both values are compared as temporal values, not as strings.
SELECT CURTIME(4); --> 10:11:12.3456
SELECT TIME_TO_SEC('10:10:10.12345'); --> 36610.12345
DISTINCTMariaDB 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_SUB()functions can now take a
TIMEexpression as an argument (not just
SELECT TIME('10:10:10') + INTERVAL 100 MICROSECOND; --> 10:10:10.000100
event_timefield in the mysql.general_log table and the
lock_timefields in the mysql.slow_log table now store values with microsecond precision.
BETWEENoperator and one of the operands is
Nis the display width, is no longer supported. It was deprecated in MySQL 4.1.0 (released on 2003-04-03).
DATETIMEvalue is compared to a
TIMEvalue, the latter is treated as a full datetime with a zero date part, similar to comparing
DATETIME, or to comparing
DECIMALnumbers. Earlier versions of MariaDB used to compare only the time part of both operands in such a case.
TIME_MShas been added to the
INFORMATION_SCHEMA.PROCESSLISTtable, 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.