Microseconds in MariaDB
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:
- when declaring arguments of stored routines.
- when specifying a return type of a stored function.
- when declaring variables.
- in a
CAST
function:create function example(x datetime(5)) returns time(4) begin declare y timestamp(6); return cast(x as time(2)); end;
%f
is used as the formatting option for microseconds in the STR_TO_DATE, DATE_FORMAT and FROM_UNIXTIME functions, for example:
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 | +-----------------------------------------------------+
Additional Information
- when comparing anything to a temporal value (
DATETIME
,TIME
,DATE
, orTIMESTAMP
), both values are compared as temporal values, not as strings. - The INFORMATION_SCHEMA.COLUMNS table has a new column
DATETIME_PRECISION
- NOW(), CURTIME(), UTC_TIMESTAMP(), UTC_TIME(), CURRENT_TIME(), CURRENT_TIMESTAMP(), LOCALTIME() and LOCALTIMESTAMP() now accept datetime precision as an optional argument. For example:
SELECT CURTIME(4); --> 10:11:12.3456
- TIME_TO_SEC() and UNIX_TIMESTAMP() preserve microseconds of the argument. These functions will return a decimal number if the result non-zero datetime precision and an integer otherwise (for backward compatibility).
SELECT TIME_TO_SEC('10:10:10.12345'); --> 36610.12345
- 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()
andDATE_SUB()
functions can now take aTIME
expression as an argument (not justDATETIME
as before).SELECT TIME('10:10:10') + INTERVAL 100 MICROSECOND; --> 10:10:10.000100
- The
event_time
field in the mysql.general_log table and thestart_time
,query_time
, andlock_time
fields in the mysql.slow_log table now store values with microsecond precision. - This patch fixed a bug when comparing a temporal value using the
BETWEEN
operator and one of the operands isNULL
. - The old syntax
TIMESTAMP(N)
, whereN
is the display width, is no longer supported. It was deprecated in MySQL 4.1.0 (released on 2003-04-03). - when a
DATETIME
value is compared to aTIME
value, the latter is treated as a full datetime with a zero date part, similar to comparingDATE
to aDATETIME
, or to comparingDECIMAL
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 theINFORMATION_SCHEMA.PROCESSLIST
table, as well as to the output ofSHOW 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 Microseconds
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.