DATETIME

Replication

• When replicating from MariaDB Xpand to MariaDB Xpand, use row-based replication when using fractional seconds.

• When replicating from MariaDB Xpand to MariaDB Enterprise Server, fractional seconds cannot be replicated safely.

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 a DATETIME and returns NULL.

• Xpand does not support casting a TIME to a DATE or a DATE to a TIME.

• When converting from a DATETIME to a DATE, 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 returns NULL.

• When the input to LAST_DAY is a DATETIME, MySQL will round to the nearest DATE before computing LAST_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 return NULL. 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() and FROM_UNIXTIME do not support input with fractional seconds.

• Boolean values (True, False, Null) are accepted in a DATETIME 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.

Formats

Xpand supports multiple DATETIME formats, including values with fractional second precision up to 6 digits (microseconds):

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', '2022-12-30'),
   ('Full year', '2022-12-30 00:00:00'),
   ('Short year', '22-12-30 00:00:00.000'),
   ('Pipe delimiters', '22|2|3 22|00|00.123456'),
   ('Forward slash delimiter', '22/12/30 00/00/00.0');

SET @@time_zone = '-07:00';

INSERT INTO datetime_formats_example VALUES
   ('Asterisk delimiter', '22*12*30 8*35*00'),
   ('Comma delimiter', '22,2,3 12,34,56.123');

When a DATETIME value is selected, the DATE_FORMAT() function can be used to control the output format. By default, the value is returned in the YYYY-MM-DD HH:MM:SS.ffffff format, which is equivalent to DATE_FORMAT(<datetime>, '%Y-%m-%d %H:%i:%S.%f'):

SELECT * FROM datetime_formats_example;

+-------------------------+----------------------------+
| description             | example                    |
+-------------------------+----------------------------+
| Date without time       | 2022-12-30 00:00:00.000000 |
| Full year               | 2022-12-30 00:00:00.000000 |
| Short year              | 2022-12-30 00:00:00.000000 |
| Pipe delimiters         | 2022-02-03 22:00:00.123456 |
| Forward slash delimiter | 2022-12-30 00:00:00.000000 |
| Asterisk delimiter      | 2022-12-30 08:35:00.000000 |
| Comma delimiter         | 2022-02-03 12:34:56.123000 |
+-------------------------+----------------------------+

Default Microsecond Precision

The default microsecond precision when unspecified is 0:

CREATE TABLE datetime_no_microseconds_example (
   description VARCHAR(30),
   example DATETIME
);

Remove Microsecond Precision

To remove microsecond precision from a DATETIME(6) column, cast it to DATETIME or DATETIME(0):

SELECT description,
   CONVERT(example, DATETIME) AS example,
   CONVERT(example, DATETIME(0)) AS example2
   FROM datetime_formats_example;

+-------------------------+---------------------+---------------------+
| description             | example             | example2            |
+-------------------------+---------------------+---------------------+
| Date without time       | 2022-12-30 00:00:00 | 2022-12-30 00:00:00 |
| Full year               | 2022-12-30 00:00:00 | 2022-12-30 00:00:00 |
| Short year              | 2022-12-30 00:00:00 | 2022-12-30 00:00:00 |
| Pipe delimiters         | 2022-02-03 22:00:00 | 2022-02-03 22:00:00 |
| Forward slash delimiter | 2022-12-30 00:00:00 | 2022-12-30 00:00:00 |
| Asterisk delimiter      | 2022-12-30 08:35:00 | 2022-12-30 08:35:00 |
| Comma delimiter         | 2022-02-03 12:34:56 | 2022-02-03 12:34:56 |
+-------------------------+---------------------+---------------------+

Range

Xpand does not reject out-of-range values for the DATETIME data type. Out-of-range DATETIME values are always accepted and replaced with "zero dates", even when sql_mode=STRICT_TRANS_TABLES is set:

CREATE TABLE datetime_range_example (
   description VARCHAR(30),
   example DATETIME(6)
);

INSERT INTO datetime_range_example VALUES
   ('Minimum datetime', '0001-01-01 00:00:00.000000'),
   ('Maximum datetime', '9999-12-31 23:59:59.999999'),
   ('Below minimum range', '0000-01-01 00:00:00.000000'),
   ('Above maximum range', '10000:00:00');

SELECT * FROM datetime_range_example;

+---------------------+----------------------------+
| description         | example                    |
+---------------------+----------------------------+
| Minimum datetime    | 0001-01-01 00:00:00.000000 |
| Maximum datetime    | 9999-12-31 23:59:59.999999 |
| Below minimum range | 0000-00-00 00:00:00.000000 |
| Above maximum range | 0000-00-00 00:00:00.000000 |
+---------------------+----------------------------+

Type Casting

Xpand does not support casting a TIME to a DATETIME and returns NULL:

SELECT CAST(TIME(NOW()) AS DATETIME);

+-------------------------------+
| CAST(TIME(NOW()) AS DATETIME) |
+-------------------------------+
| NULL                          |
+-------------------------------+

When converting from a DATETIME to a DATE, the time portion is discarded:

SELECT CAST(NOW() AS DATE);

+---------------------+
| CAST(NOW() AS DATE) |
+---------------------+
| 2022-10-23          |
+---------------------+