DATETIME
Syntax
DATETIME [(microsecond precision)]
Description
A date and time combination. The supported range is
'1000-01-01 00:00:00.000000
' to '9999-12-31 23:59:59.999999
'.
MariaDB displays DATETIME
values in 'YYYY-MM-DD HH:MM:SS
' format, but
allows assignment of values to DATETIME
columns using either strings or
numbers. For details, see date and time literals.
The microsecond precision can be from 0-6. If not specified 0 is used. Microseconds have been available since MariaDB 5.3.
'0000-00-00
' is a permitted special value (zero-date), unless the NO_ZERO_DATE
SQL_MODE is used. Also, individual components of a date can be set to 0 (for example: '2015-00-12
'), unless the NO_ZERO_IN_DATE
SQL_MODE is used. In many cases, the result of en expression involving a zero-date, or a date with zero-parts, is NULL
. If the ALLOW_INVALID_DATES
SQL_MODE is enabled, if the day part is in the range between 1 and 31, the date does not produce any error, even for months that have less than 31 days.
MariaDB starting with 10.0.1
Since MariaDB 10.0.1, DATETIME columns also accept CURRENT_TIMESTAMP as the default value.
MariaDB 10.1.2 introduced the --mysql56-temporal-format option, on by default, which allows MariaDB to store DATETMEs using the same low-level format MySQL 5.6 uses. For more information, see Internal Format, below.
For storage requirements, see Data Type Storage Requirements.
Oracle Mode
MariaDB starting with 10.3
In Oracle mode from MariaDB 10.3, DATE
with a time portion is a synonym for DATETIME
.
Internal Format
In MariaDB 10.1.2 a new temporal format was introduced from MySQL 5.6 that alters how the TIME
, DATETIME
and TIMESTAMP
columns operate at lower levels. These changes allow these temporal data types to have fractional parts and negative values. You can disable this feature using the mysql56_temporal_format
system variable.
Tables that include TIMESTAMP
values that were created on an older version of MariaDB or that were created while the mysql56_temporal_format
system variable was disabled continue to store data using the older data type format.
In order to update table columns from the older format to the newer format, execute an ALTER TABLE... MODIFY COLUMN
statement that changes the column to the *same* data type.
For instance, if you have a DATETIME
column in your table:
SHOW VARIABLES LIKE 'mysql56_temporal_format'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | mysql56_temporal_format | ON | +-------------------------+-------+ ALTER TABLE example_table MODIFY ts_col DATETIME;
When MariaDB executes the ALTER TABLE
statement, it converts the data from the older temporal format to the newer one.
In the event that you have several tables and columns using temporal data types that you want to switch over to the new format, make sure the system variable is enabled, then perform a dump and restore using mysqldump
. The columns using relevant temporal data types are restored using the new temporal format.
Examples
CREATE TABLE t1 (d DATETIME); INSERT INTO t1 VALUES ("2011-03-11"), ("2012-04-19 13:08:22"), ("2013-07-18 13:44:22.123456"); SELECT * FROM t1; +---------------------+ | d | +---------------------+ | 2011-03-11 00:00:00 | | 2012-04-19 13:08:22 | | 2013-07-18 13:44:22 | +---------------------+
CREATE TABLE t2 (d DATETIME(6)); INSERT INTO t2 VALUES ("2011-03-11"), ("2012-04-19 13:08:22"), ("2013-07-18 13:44:22.123456"); SELECT * FROM t2; +----------------------------+ | d | +----------------------------+ | 2011-03-11 00:00:00.000000 | | 2012-04-19 13:08:22.000000 | | 2013-07-18 13:44:22.123456 | +----------------------------++
Strings used in datetime context are automatically converted to datetime(6). If you want to have a datetime without seconds, you should use CONVERT(..,datetime).
SELECT CONVERT('2007-11-30 10:30:19',datetime); +-----------------------------------------+ | CONVERT('2007-11-30 10:30:19',datetime) | +-----------------------------------------+ | 2007-11-30 10:30:19 | +-----------------------------------------+ SELECT CONVERT('2007-11-30 10:30:19',datetime(6)); +--------------------------------------------+ | CONVERT('2007-11-30 10:30:19',datetime(6)) | +--------------------------------------------+ | 2007-11-30 10:30:19.000000 | +--------------------------------------------+