DATETIME
Contents
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 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
.
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 | +--------------------------------------------+