TIMESTAMP Type
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 1970 and 2038.
See also: Data Types for MariaDB Xpand 6.0 and in 5.3
DETAILS
Xpand supports storing fractional second precision for TIMESTAMP
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
CREATE TABLE timestamp_formats_example (
description VARCHAR(30),
example TIMESTAMP(6)
);
-- The time zone affects the values
SET @@time_zone = '+00:00';
INSERT INTO timestamp_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 timestamp_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:
SET @@time_zone = '+00:00';
SELECT * FROM timestamp_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 15:35:00.000000 |
| Comma delimiter | 2019-02-03 19: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:
CHANGE HISTORY
Release Series | History |
---|---|
6.0 |
|
5.3 |
|