Xpand supports storing fractional second precision for TIMESTAMP, DATETIME, and TIME with up to microsecond (6 digit) precision. Xpand closely matches MySQL except for the following caveats.
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 the 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.
Xpand recommends using RBR replication when utilizing fractional seconds.