DATETIME
Year, month, day, hours, minutes, seconds with dates from 1000 to 9999
See also: Data Types in 10.5 ES, in 10.5 CS, in 10.4 ES, in 10.4 CS, in 10.3 ES, in 10.3 CS, in 10.2 ES, and in 10.2 CS
DETAILS
CHANGE HISTORY
Release Series |
History |
---|---|
10.5 Enterprise |
|
10.5 Community |
|
10.4 Enterprise |
|
10.4 Community |
|
10.3 Enterprise |
|
10.3 Community |
|
10.2 Enterprise |
|
10.2 Community |
|
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 |
+-------------------------+---------------------+
EXTERNAL REFERENCES
Additional information on this topic may be found in the MariaDB Public Knowledge Base.
Information specific to MariaDB SkySQL can be found on the DATETIME page in the SkySQL Documentation.