Store date and time combinations. This type stores values in 'YYYY-MM-DD HH:MM:SS' format, with a range from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
A date and time combination.
MariaDB displays DATETIME values in 'YYYY-MM-DD HH:MM:SS.ffffff' format, but allows assignment of values to DATETIME columns using either strings or numbers. For details, see .
DATETIME columns also accept as the default value.
The option, on by default, allows MariaDB to store DATETME values using the same low-level format MySQL 5.6 uses. For more information, see , below.
For storage requirements, see .
MariaDB stores values that use the DATETIME data type in a format that supports values between 1000-01-01 00:00:00.000000 and 9999-12-31 23:59:59.999999.
MariaDB can also store with a precision between 0 and 6. If no microsecond precision is specified, then 0 is used by default.
MariaDB also supports '0000-00-00' as a special zero-date value, unless is specified in the . Similarly, individual components of a date can be set to 0 (for example: '2015-00-12'), unless is specified in the . In many cases, the result of en expression involving a zero-date, or a date with zero-parts, is NULL. If the 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.
In , DATE with a time portion is a synonym for DATETIME. See also .
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 system variable.
Tables that include TIMESTAMP values that were created on an older version of MariaDB or that were created while the 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 statement that changes the column to the same data type. This change may be needed if you want to export the table's tablespace and import it onto a server that has mysql56_temporal_format=ON set (see ).
For instance, if you have a DATETIME column in your table:
When MariaDB executes the 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.
Columns with old temporal formats are marked with a /* mariadb-5.3 */ comment in the output of , , statements, as well as in the COLUMN_TYPE column of the .
Strings used in datetime context are automatically converted to datetime(6). If you want to have a datetime without seconds, you should use .
The resulting data would look like this:
The default microsecond precision when unspecified is 0, and you can use that in a cast in order to trim off stored microseconds:
data type qualifier
This page is licensed: GPLv2, originally from
DATETIME [(microsecond precision)]Store date values. This type stores dates in 'YYYY-MM-DD' format, with a supported range from '1000-01-01' to '9999-12-31'.
A date. The supported range is '1000-01-01' to '9999-12-31'. MariaDB displays DATE values in 'YYYY-MM-DD' format, but can be assigned dates in looser formats, including strings or numbers, as long as they make sense. These include a short year, YY-MM-DD, no delimiters, YYMMDD, or any other acceptable delimiter, for example YYYY/MM/DD. For details, see .
'0000-00-00' is a permitted special value (zero-date), unless the is used. Also, individual components of a date can be set to 0 (for example: '2015-00-12'), unless the is used. In many cases, the result of en expression involving a zero-date, or a date with zero-parts, is NULL. If the 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.
In , DATE with a time portion is a synonym for . See also .
The resulting data look like this:
If SQL_MODE is strict (the default), the example above generates the following error and no values are inserted:
If SQL_MODE is not strict, the example above generates a warning and (possibly modified) values are inserted. The Below minimum range value is accepted because it contains a zero component. The Above maximum range value is truncated since it is an unacceptable date.
The resulting data would look like this:
When using a date value in an expression, such as DATE_ADD(), the following illustrates that a NULL is generated when a date value is not a real date and when a real date overflows:
The following example enhances the SQL_MODE to ensure that ALLOW_INVALID_DATES is set and illustrates the difference between a day that is outside the range of 1 to 31 and one that is just too large for its month:
The resulting data would look like this:
data type qualifier
This page is licensed: GPLv2, originally from
SHOW VARIABLES LIKE 'mysql56_temporal_format';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| mysql56_temporal_format | ON |
+-------------------------+-------+
ALTER TABLE example_table MODIFY ts_col DATETIME;SHOW CREATE TABLE mariadb5312_datetime\G
*************************** 1. row ***************************
Table: mariadb5312_datetime
Create Table: CREATE TABLE `mariadb5312_datetime` (
`dt0` datetime /* mariadb-5.3 */ DEFAULT NULL,
`dt6` datetime(6) /* mariadb-5.3 */ DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1CREATE 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 |
+----------------------------++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 |
+--------------------------------------------+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');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 |
+-------------------------+----------------------------+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 |
+-------------------------+---------------------+DATECREATE TABLE t1 (d DATE);
INSERT INTO t1 VALUES ("2010-01-12"), ("2011-2-28"), ('120314'),('13*04*21');
SELECT * FROM t1;
+------------+
| d |
+------------+
| 2010-01-12 |
| 2011-02-28 |
| 2012-03-14 |
| 2013-04-21 |
+------------+CREATE TABLE date_formats_example (
description VARCHAR(30),
example DATE
);INSERT INTO date_formats_example VALUES
('Full year', '2022-12-30'),
('Short year', '22-12-30'),
('Short year no delimiters', '221230'),
('No delimiters', '20221230'),
('Pipe delimiters', '22|2|3'),
('Forward slash delimiter', '22/12/30'),
('Backward slash delimiter', '22\12\30'),
('Asterisk delimiter', '22*12*30'),
('Comma delimiter', '22,2,3');SELECT * FROM date_formats_example;+--------------------------+------------+
| description | example |
+--------------------------+------------+
| Full year | 2022-12-30 |
| Short year | 2022-12-30 |
| Short year no delimiters | 2022-12-30 |
| No delimiters | 2022-12-30 |
| Pipe delimiters | 2022-02-03 |
| Forward slash delimiter | 2022-12-30 |
| Backward slash delimiter | 2022-12-30 |
| Asterisk delimiter | 2022-12-30 |
| Comma delimiter | 2022-02-03 |
+--------------------------+------------+CREATE TABLE date_range_example (
description VARCHAR(30),
example DATE
);INSERT INTO date_range_example VALUES
('Minimum date', '0001-01-01'),
('Maximum date', '9999-12-31'),
('Below minimum range', '0000*1*1'),
('Above maximum range', '10000,12,31');ERROR 1292 (22007): Incorrect date value: '10000,12,31' for column `test`.`date_range_example`.`example` at row 4Warning (Code 1265): Data truncated for column 'example' at row 4SELECT * FROM date_range_example;+---------------------+------------+
| description | example |
+---------------------+------------+
| Minimum date | 0001-01-01 |
| Maximum date | 9999-12-31 |
| Below minimum range | 0000-01-01 |
| Above maximum range | 0000-00-00 |
+---------------------+------------+SELECT example, DATE_ADD(example, INTERVAL 1 DAY)
FROM date_range_example;+------------+-----------------------------------+
| example | DATE_ADD(example, INTERVAL 1 DAY) |
+------------+-----------------------------------+
| 0001-01-01 | 0001-01-02 |
| 9999-12-31 | NULL |
| 0000-01-01 | NULL |
| 0000-00-00 | NULL |
+------------+-----------------------------------+Warning (Code 1441): Datetime function: datetime field overflow
Warning (Code 1441): Datetime function: datetime field overflow
Warning (Code 1292): Incorrect datetime value: '0000-00-00'-- Disable STRICT_TRANS_TABLES and enable ALLOW_INVALID_DATES
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
SET sql_mode=(SELECT CONCAT(@@sql_mode, ',ALLOW_INVALID_DATES'));
<</code>>
<<sql>>
INSERT INTO date_range_example VALUES
('day is invalid for all months', '2019-12-32'),
('day is just large for February', '2019-02-31');Warning (Code 1265): Data truncated for column 'example' at row 1SELECT * FROM date_range_example;+--------------------------------+------------+
| description | example |
+--------------------------------+------------+
| day is invalid for all months | 0000-00-00 |
| day is just large for February | 2019-02-31 |
+--------------------------------+------------+Store temporal values. This section covers data types for dates, times, and timestamps, including DATE, DATETIME, TIMESTAMP, TIME, and YEAR.
Store time values. This type handles time durations or time of day, ranging from '-838:59:59' to '838:59:59' with optional microsecond precision.
A time. The range is '-838:59:59.999999' to '838:59:59.999999'. can be from 0-6; if not specified 0 is used.
MariaDB displays TIME values in 'HH:MM:SS.ssssss' format, but allows assignment of times in looser formats, including 'D HH:MM:SS', 'HH:MM:SS', 'HH:MM', 'D HH:MM', 'D HH', 'SS', or 'HHMMSS', as well as permitting dropping of any leading zeros when a delimiter is provided, for example '3:9:10'. For details, see .
The option, on by default, allows MariaDB to store TIME values using the same low-level format MySQL 5.6 uses.
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 system variable.
Tables that include TIMESTAMP values that were created on an older version of MariaDB or that were created while the 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 statement that changes the column to the same data type. This change may be needed if you want to export the table's tablespace and import it onto a server that has mysql56_temporal_format=ON set (see ).
For instance, if you have a TIME column in your table:
When MariaDB executes the 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 mariadb-dump. The columns using relevant temporal data types are restored using the new temporal format.
Columns with old temporal formats are marked with a /* mariadb-5.3 */ comment in the output of , , statements, as well as in the COLUMN_TYPE column of the .
Columns with the current format are not marked with a comment.
Time example:
The resulting data look like this:
This page is licensed: GPLv2, originally from
Synonym for YEAR. This keyword is an alias used for declaring a column to store year values.
See YEAR.
CREATE TABLE sql_tsi_year_example (
example SQL_TSI_YEAR
);SHOW CREATE TABLE sql_tsi_year_example\G*************************** 1. row ***************************
Table: sql_tsi_year_example
Create Table: CREATE TABLE `sql_tsi_year_example` (
`example` year(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1This page is: Copyright © 2025 MariaDB. All rights reserved.
TIME [(<microsecond precision>)]SHOW VARIABLES LIKE 'mysql56_temporal_format';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| mysql56_temporal_format | ON |
+-------------------------+-------+
ALTER TABLE example_table MODIFY ts_col TIME;SHOW CREATE TABLE mariadb5312_time\G
*************************** 1. row ***************************
Table: mariadb5312_time
Create Table: CREATE TABLE `mariadb5312_time` (
`t0` time /* mariadb-5.3 */ DEFAULT NULL,
`t6` time(6) /* mariadb-5.3 */ DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1INSERT INTO time VALUES ('90:00:00'), ('800:00:00'), (800), (22), (151413), ('9:6:3'), ('12 09');
SELECT * FROM time;
+-----------+
| t |
+-----------+
| 90:00:00 |
| 800:00:00 |
| 00:08:00 |
| 00:00:22 |
| 15:14:13 |
| 09:06:03 |
| 297:00:00 |
+-----------+CREATE TABLE time_example (
description VARCHAR(30),
example TIME(6)
);INSERT INTO time_example VALUES
('HH:MM:SS', '12:34:56'),
('HHMMSS', '123456'),
('SS.microsec', '42.123456');SELECT * FROM time_example;+-------------+-----------------+
| description | example |
+-------------+-----------------+
| HH:MM:SS | 12:34:56.000000 |
| HHMMSS | 12:34:56.000000 |
| SS.microsec | 00:00:42.123456 |
+-------------+-----------------+Store timestamp values. This type stores the number of seconds since the Unix Epoch, with a range from '1970-01-01' to '2038-01-19' in UTC.
This page is about the TIMESTAMP data type. For the timestamp function, see TIMESTAMP FUNCTION.
A timestamp in the format YYYY-MM-DD HH:MM:SS.ffffff.
The timestamp field is generally used to define at which moment in time a row was added or updated and by default will automatically be assigned the current datetime when a record is inserted or updated. The automatic properties only apply to the first TIMESTAMP in the record; subsequent TIMESTAMP columns will not be changed.
MariaDB includes the option, on by default, which allows MariaDB to store TIMESTAMP values using the same low-level format MySQL 5.6 uses.
For more information, see .
MariaDB stores values that use the TIMESTAMP data type as the number of seconds since '1970-01-01 00:00:00' (). This means that the TIMESTAMP data type can hold values between '1970-01-01 00:00:01' () and '2106-02-07 06:28:15 UTC'.
MariaDB stores values that use the TIMESTAMP data type as the number of seconds since '1970-01-01 00:00:00' (). This means that the TIMESTAMP data type can hold values between '1970-01-01 00:00:01' () and '2038-01-19 03:14:07' ().
MariaDB can also store with a precision between 0 and 6. If no microsecond precision is specified, then 0 is used by default.
MariaDB has special behavior for the first column that uses the TIMESTAMP data type in a specific table when the system variable is not set (which was the default until ). For the first column that uses the TIMESTAMP data type in a specific table, MariaDB automatically assigns the following properties to the column:
DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP
This means that if the column is not explicitly assigned a value in an INSERT or UPDATE query, then MariaDB will automatically initialize the column's value with the current date and time.
This automatic initialization for INSERT and UPDATE queries can also be explicitly enabled for a column that uses the TIMESTAMP data type by specifying the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses for the column. In these clauses, any synonym of is accepted, including CURRENT_TIMESTAMP(), NOW(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, and LOCALTIMESTAMP().
This automatic initialization for INSERT queries can also be explicitly disabled for a column that uses the TIMESTAMP data type by specifying a constant DEFAULT value. For example, DEFAULT 0.
This automatic initialization for UPDATE queries can also be explicitly disabled for a column that uses the TIMESTAMP data type by specifying a DEFAULT clause for the column, but no ON UPDATE clause. If a DEFAULT clause is explicitly specified for a column that uses the TIMESTAMP data type, but an ON UPDATE clause is not specified for the column, then the timestamp value will not automatically change when an UPDATE statement is executed.
MariaDB also has special behavior if NULL is assigned to column that uses the TIMESTAMP data type. If the column is assigned the NULL value in an INSERT or UPDATE query, then MariaDB will automatically initialize the column's value with the current date and time. For details, see .
This automatic initialization for NULL values can also be explicitly disabled for a column that uses the TIMESTAMP data type by specifying the NULL attribute for the column. In this case, if the column's value is set to NULL, then the column's value will actually be set to NULL.
If a column uses the TIMESTAMP data type, then any inserted values are converted from the session's time zone to when stored, and converted back to the session's time zone when retrieved.
MariaDB validates TIMESTAMP literals against the session's time zone. For example, if a specific time range never occurred in a specific time zone due to daylight savings time, then TIMESTAMP values within that range would be invalid for that time zone.
MariaDB does not store any time zone identifier with the value of the TIMESTAMP data type. See for more information.
MariaDB does not support time zone literals that contain time zone identifiers. See for more information.
Because the TIMESTAMP value is stored as Epoch Seconds, the timestamp value '1970-01-01 00:00:00' (UTC) is reserved since the second #0 is used to represent '0000-00-00 00:00:00'.
If the is set to MAXDB, TIMESTAMP fields will be silently converted to .
A 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 system variable.
Tables that include TIMESTAMP values that were created on an older version of MariaDB or that were created while the 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 statement that changes the column to the same data type. This change may be needed if you want to export the table's tablespace and import it onto a server that has mysql56_temporal_format=ON set (see ).
For instance, if you have a TIMESTAMP column in your table:
When MariaDB executes the 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 . The columns using relevant temporal data types are restored using the new temporal format.
Columns with old temporal formats are marked with a /* mariadb-5.3 */ comment in the output of , , statements, as well as in the COLUMN_TYPE column of the .
Columns with old temporal formats are not marked with a /* mariadb-5.3 */ comment in the output of , , statements, as well as in the COLUMN_TYPE column of the .
Converting to Unix epoch:
Update also changes the timestamp:
Default NULL:
Only the first timestamp is automatically inserted and updated:
Explicitly setting a timestamp with the function:
Specifying the timestamp as NOT NULL:
This page is licensed: CC BY-SA / Gnu FDL
TIMESTAMP [(<microsecond precision)]SHOW VARIABLES LIKE 'mysql56_temporal_format';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| mysql56_temporal_format | ON |
+-------------------------+-------+
ALTER TABLE example_table MODIFY ts_col TIMESTAMP;SHOW CREATE TABLE mariadb5312_timestamp\G
*************************** 1. row ***************************
Table: mariadb5312_timestamp
Create Table: CREATE TABLE `mariadb5312_timestamp` (
`ts0` timestamp /* mariadb-5.3 */ NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`ts6` timestamp(6) /* mariadb-5.3 */ NOT NULL DEFAULT '0000-00-00 00:00:00.000000'
) ENGINE=MyISAM DEFAULT CHARSET=latin1CREATE TABLE t (id INT, ts TIMESTAMP);
DESC t;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id | int(11) | YES | | NULL | |
| ts | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
INSERT INTO t(id) VALUES (1),(2);
SELECT * FROM t;
+------+---------------------+
| id | ts |
+------+---------------------+
| 1 | 2013-07-22 12:50:05 |
| 2 | 2013-07-22 12:50:05 |
+------+---------------------+
INSERT INTO t VALUES (3,NULL),(4,'2001-07-22 12:12:12');
SELECT * FROM t;
+------+---------------------+
| id | ts |
+------+---------------------+
| 1 | 2013-07-22 12:50:05 |
| 2 | 2013-07-22 12:50:05 |
| 3 | 2013-07-22 12:51:56 |
| 4 | 2001-07-22 12:12:12 |
+------+---------------------+SELECT ts, UNIX_TIMESTAMP(ts) FROM t;
+---------------------+--------------------+
| ts | UNIX_TIMESTAMP(ts) |
+---------------------+--------------------+
| 2013-07-22 12:50:05 | 1374490205 |
| 2013-07-22 12:50:05 | 1374490205 |
| 2013-07-22 12:51:56 | 1374490316 |
| 2001-07-22 12:12:12 | 995796732 |
+---------------------+--------------------+UPDATE t set id=5 WHERE id=1;
SELECT * FROM t;
+------+---------------------+
| id | ts |
+------+---------------------+
| 5 | 2013-07-22 14:52:33 |
| 2 | 2013-07-22 12:50:05 |
| 3 | 2013-07-22 12:51:56 |
| 4 | 2001-07-22 12:12:12 |
+------+---------------------+CREATE TABLE t2 (id INT, ts TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP);
INSERT INTO t(id) VALUES (1),(2);
SELECT * FROM t2;
INSERT INTO t2(id) VALUES (1),(2);
SELECT * FROM t2;
+------+------+
| id | ts |
+------+------+
| 1 | NULL |
| 2 | NULL |
+------+------+
UPDATE t2 SET id=3 WHERE id=1;
SELECT * FROM t2;
+------+---------------------+
| id | ts |
+------+---------------------+
| 3 | 2013-07-22 15:32:22 |
| 2 | NULL |
+------+---------------------+CREATE TABLE t3 (id INT, ts1 TIMESTAMP, ts2 TIMESTAMP);
INSERT INTO t3(id) VALUES (1),(2);
SELECT * FROM t3;
+------+---------------------+---------------------+
| id | ts1 | ts2 |
+------+---------------------+---------------------+
| 1 | 2013-07-22 15:35:07 | 0000-00-00 00:00:00 |
| 2 | 2013-07-22 15:35:07 | 0000-00-00 00:00:00 |
+------+---------------------+---------------------+
DESC t3;
+-------+-----------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------------------+-----------------------------+
| id | int(11) | YES | | NULL | |
| ts1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| ts2 | timestamp | NO | | 0000-00-00 00:00:00 | |
+-------+-----------+------+-----+---------------------+-----------------------------+INSERT INTO t3(id,ts2) VALUES (3,CURRENT_TIMESTAMP());
SELECT * FROM t3;
+------+---------------------+---------------------+
| id | ts1 | ts2 |
+------+---------------------+---------------------+
| 1 | 2013-07-22 15:35:07 | 0000-00-00 00:00:00 |
| 2 | 2013-07-22 15:35:07 | 0000-00-00 00:00:00 |
| 3 | 2013-07-22 15:38:52 | 2013-07-22 15:38:52 |
+------+---------------------+---------------------+CREATE TABLE t4 (id INT, ts TIMESTAMP NOT NULL);
INSERT INTO t4(id) VALUES (1);
SELECT SLEEP(1);
INSERT INTO t4(id,ts) VALUES (2,NULL);
SELECT * FROM t4;Store year values. This type stores a year in 2-digit or 4-digit format, supporting values from 1901 to 2155, and 0000.
A year in two-digit or four-digit format. The default is four-digit format.
In four-digit format, the allowable values are 1901 to 2155, and 0000. In two-digit format, the allowable values are 70 to 69, representing years from 1970 to 2069. MariaDB displays YEAR values in YYYY format, but allows you to assign values to YEAR columns using either strings or numbers.
Inserting numeric zero has a different result for YEAR(4) and YEAR(2). For YEAR(2), the value 00 reflects the year 2000. For YEAR(4), the value 0000 reflects the year zero. This only applies to numeric zero. String zero always reflects the year 2000.
Accepting a string or a number:
With set — values out of range:
With unset — values out of range:
Truncating:
Difference between YEAR(2) and YEAR(4), and string and numeric zero:
This page is licensed: GPLv2, originally from
YEAR[(4)]CREATE TABLE y(y YEAR);
INSERT INTO y VALUES (1990),('2012');
SELECT * FROM y;
+------+
| y |
+------+
| 1990 |
| 2012 |
+------+INSERT INTO y VALUES (1005),('3080');
ERROR 1264 (22003): Out of range value for column 'y' at row 1
INSERT INTO y VALUES ('2013-12-12');
ERROR 1265 (01000): Data truncated for column 'y' at row 1
SELECT * FROM y;
+------+
| y |
+------+
| 1990 |
| 2012 |
+------+INSERT INTO y VALUES (1005),('3080');
Query OK, 2 rows affected, 2 warnings (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 2
SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'y' at row 1 |
| Warning | 1264 | Out of range value for column 'y' at row 2 |
+---------+------+--------------------------------------------+
SELECT * FROM y;
+------+
| y |
+------+
| 1990 |
| 2012 |
| 0000 |
| 0000 |
+------+INSERT INTO y VALUES ('2013-12-12');
Query OK, 1 row affected, 1 warning (0.05 sec)
SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'y' at row 1 |
+---------+------+----------------------------------------+
SELECT * FROM y;
+------+
| y |
+------+
| 1990 |
| 2012 |
| 0000 |
| 0000 |
| 2013 |
+------+CREATE TABLE y2(y YEAR(4), y2 YEAR(2));
Query OK, 0 rows affected, 1 warning (0.40 sec)
Note (Code 1287): 'YEAR(2)' is deprecated and will be removed in a future release.
Please use YEAR(4) instead
INSERT INTO y2 VALUES(0,0),('0','0');
SELECT YEAR(y),YEAR(y2) FROM y2;
+---------+----------+
| YEAR(y) | YEAR(y2) |
+---------+----------+
| 0 | 2000 |
| 2000 | 2000 |
+---------+----------+