TIMESTAMP Type

Overview

Year, month, day, hours, minutes, seconds with dates from 1970 to 2038.

See also: Data Types in 10.6 ES, in 10.5 ES, and in 10.4 ES

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

10.6 Enterprise

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

EXTERNAL REFERENCES