DATETIME
This page is part of MariaDB's MariaDB Documentation.
The parent of this page is: Data Types for MariaDB Xpand
Topics on this page:
Overview
Year, month, day, hours, minutes, seconds, and microseconds, and supporting dates between the years 1000 and 9999.
See also: Data Types for MariaDB Xpand 6.1, in 6.0, and in 5.3
DETAILS
Xpand supports storing fractional second precision for TIMESTAMP
, DATETIME
, and TIME
with up to microsecond (6 digit) precision.
MySQL Compatibility
As with MySQL, Xpand will interpret input values supplied to DATE
and TIME
functions and convert them to a standard format. However, unpredictable results may occur if values are provided in other formats.
Xpand matches MySQL with the exception of:
Xpand does not support casting a
TIME
to aDATETIME
and returnsNULL
.Xpand does not support casting a
TIME
to aDATE
or aDATE
to aTIME.
When converting from a
DATETIME
to aDATE
, Xpand will discard the the time portion. MySQL will take fractional seconds into account and round the time part.Casting negative value to
DATETIME
will return a zero date. MySQL returnsNULL.
When the input to
LAST_DAY
is aDATETIME
, MySQL will round to the nearestDATE
before computingLAST_DAY
where Xpand does not. For example,LAST_DAY('2013-01-31 23:59:59.999999')
returns'2017-01-31'
on Xpand and'2017-02-01'
on MySQL.If an invalid format string is supplied to
STR_TO_DATE
, Xpand will returnNULL
. MySQL ignores extra characters at the end of format string.The results when using
EXTRACT
with compound units (e.g.,DAY_SECOND
,DAY_MICROSECOND
) may exclude some of the requested units or return incorrect results.Output from datetime functions (e.g.,
NOW()
,INTERVAL() FROM_UNIXTIME()
,SUBTIME()
) display microsecond precision by default.UNIX_TIMESTAMP()
andFROM_UNIXTIME
do not support input with fractional seconds.Boolean values (
True
,False
,Null
) are accepted in aDATETIME
column. This matches the behavior for MySQL v5.5. MySQL v5.6 provides an error message.On Xpand, specifying a zero value for day, month, or year within a date will result in a zero date ('0000-00-00'). MySQL will issue an error (if the sql_
mode for NO_ZERO_IN_DATE
) is set, or allow zero value for the specific portion of the date.
Replication Compatibility
MariaDB recommends using row-based replication when utilizing fractional seconds.
Fractional seconds cannot be replicated safely from Xpand to MariaDB Enterprise Server.
EXAMPLES
DATETIME Format
CREATE TABLE datetime_formats_example (
description VARCHAR(30),
example DATETIME(6)
);
-- The time zone has no effect on the values
SET @@time_zone = '+00:00';
INSERT INTO datetime_formats_example VALUES
('Date without time', '2019-12-30'),
('Full year', '2019-12-30 00:00:00'),
('Short year', '19-12-30 00:00:00.000'),
('Pipe delimiters', '19|2|3 19|00|00.123456'),
('Forward slash delimiter', '19/12/30 00/00/00.0');
SET @@time_zone = '-07:00';
INSERT INTO datetime_formats_example VALUES
('Asterisk delimiter', '19*12*30 8*35*00'),
('Comma delimiter', '19,2,3 12,34,56.123');
The resulting data would look like this:
SELECT * FROM datetime_formats_example;
+-------------------------+----------------------------+
| description | example |
+-------------------------+----------------------------+
| Date without time | 2019-12-30 00:00:00.000000 |
| Full year | 2019-12-30 00:00:00.000000 |
| Short year | 2019-12-30 00:00:00.000000 |
| Pipe delimiters | 2019-02-03 19:00:00.123456 |
| Forward slash delimiter | 2019-12-30 00:00:00.000000 |
| Asterisk delimiter | 2019-12-30 08:35:00.000000 |
| Comma delimiter | 2019-02-03 12:34:56.123000 |
+-------------------------+----------------------------+
The default microsecond precision when unspecified is 0, and you can use that in a cast in order to trim off stored microseconds:
SELECT description, CONVERT(example, DATETIME) AS example
FROM datetime_formats_example;
+-------------------------+---------------------+
| description | example |
+-------------------------+---------------------+
| Date without time | 2019-12-30 00:00:00 |
| Full year | 2019-12-30 00:00:00 |
| Short year | 2019-12-30 00:00:00 |
| Pipe delimiters | 2019-02-03 19:00:00 |
| Forward slash delimiter | 2019-12-30 00:00:00 |
| Asterisk delimiter | 2019-12-30 08:35:00 |
| Comma delimiter | 2019-02-03 12:34:56 |
+-------------------------+---------------------+
CHANGE HISTORY
Release Series | History |
---|---|
6.1 |
|
6.0 |
|
5.3 |
|