Fractional Seconds in Time Values
Before MariaDB 5.6.4, the instances are limited in which a fractional seconds part is permitted in temporal values. A trailing fractional part is permissible in contexts such as literal values, and in the arguments to or return values from some temporal functions. Example:
mysql> SELECT MICROSECOND('2010-12-10 14:12:09.019473');
+-------------------------------------------+
| MICROSECOND('2010-12-10 14:12:09.019473') |
+-------------------------------------------+
| 19473 |
+-------------------------------------------+
However, when MariaDB stores a value into a column of any temporal data type, it discards any fractional part and does not store it.
MySQL 5.6.4 and up expands fractional seconds support for TIME
, DATETIME
, and TIMESTAMP
values, with up to microseconds (6 digits) precision:
- To define a column that includes a fractional seconds part, use the syntax
, wheretype_name
(fsp
)type_name
isTIME
,DATETIME
, orTIMESTAMP
, andfsp
is the fractional seconds precision. For example:CREATE TABLE t1 (t TIME(3), dt DATETIME(6));
The
fsp
value, if given, must be in the range 0 to 6. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. (This differs from the standard SQL default of 6, for compatibility with previous MariaDB versions.) - Functions that take temporal arguments accept values with fractional seconds. Return values from temporal functions include fractional seconds as appropriate.
- Syntax for temporal literals produces temporal values:
DATE '
,str
'TIME '
, andstr
'TIMESTAMP '
, and the ODBC-syntax equivalents. Previously, the temporal type keyword was ignored and these constructs produced the string value. See Standard SQL and ODBC Date and Time Literalsstr
'
In some cases, previously accepted syntax may produce different results. The following items indicate where existing code may need to be changed to avoid problems:
- Some expressions produce results that differ from previous results. Examples: The
timestamp
system variable returns a value that includes a microseconds fractional part rather than an integer. Functions that return a result that includes the current time (such asCURTIME()
,SYSDATE()
, orUTC_TIMESTAMP()
) interpret an argument as anfsp
value and the return value includes a fractional seconds part of that many digits. Previously, these functions permitted an argument but ignored it. TIME
values are converted toDATETIME
by adding the time to the current date. (This means that the date part of the result differs from the current date if the time value is outside the range from'00:00:00'
to'23:59:59'
.) Previously, conversion ofTIME
values toDATETIME
was unreliable. See , "Conversion Between Date and Time Types".TIMESTAMP(
was permitted in old MariaDB versions, butN
)N
was a display width rather than fractional seconds precision. Support for this behavior was removed in MariaDB 5.5.3, so applications that are reasonably up to date should not be subject to this issue. Otherwise, code must be rewritten.