DATETIME

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.

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 |
+-------------------------+---------------------+