Menu:
two-digit-years:: 2-Digit Years in Dates
The date and time data types for representing temporal values are note 'DATE': datetime, note 'TIME': time, note 'DATETIME': datetime, note 'TIMESTAMP': datetime, and note 'YEAR': year. Each temporal type has a range of valid values, as well as a 'zero' value that may be used when you specify an invalid value that MySQL cannot represent. The note 'TIMESTAMP': datetime. and note 'DATETIME': datetime. types have special automatic updating behavior, described in note timestamp-initialization::.
For information about storage requirements of the temporal data types, see *note storage-requirements::.
For descriptions of functions that operate on temporal values, see *note date-and-time-functions::.
Keep in mind these general considerations when working with date and time types:
MySQL retrieves values for a given date or time type in a standard output format, but it attempts to interpret a variety of formats for input values that you supply (for example, when you specify a value to be assigned to or compared to a date or time type). For a description of the permitted formats for date and time types, see *note date-and-time-literals::. It is expected that you supply valid values. Unpredictable results may occur if you use values in other formats.
Although MySQL tries to interpret values in several formats, date parts must always be given in year-month-day order (for example, ''98-09-04''), rather than in the month-day-year or day-month-year orders commonly used elsewhere (for example, ''09-04-98'', ''04-09-98''). To convert strings in other orders to year-month-day order, the 'STR_TO_DATE()' function may be useful.
Dates containing 2-digit year values are ambiguous because the century is unknown. MySQL interprets 2-digit year values using these rules:
* Year values in the range '70-99' become '1970-1999'.
* Year values in the range '00-69' become '2000-2069'.See also *note two-digit-years::.
Conversion of values from one temporal type to another occurs according to the rules in *note date-and-time-type-conversion::.
MySQL automatically converts a date or time value to a number if the value is used in numeric context and vice versa.
By default, when MySQL encounters a value for a date or time type that is out of range or otherwise invalid for the type, it converts the value to the 'zero' value for that type. The exception is that out-of-range note 'TIME': time. values are clipped to the appropriate endpoint of the note 'TIME': time. range.
By setting the SQL mode to the appropriate value, you can specify more exactly what kind of dates you want MySQL to support. (See *note sql-mode::.) You can get MySQL to accept certain dates, such as ''2009-11-31'', by enabling the 'ALLOW_INVALID_DATES' SQL mode. This is useful when you want to store a 'possibly wrong' value which the user has specified (for example, in a web form) in the database for future processing. Under this mode, MySQL verifies only that the month is in the range from 1 to 12 and that the day is in the range from 1 to 31.
MySQL permits you to store dates where the day or month and day are zero in a note 'DATE': datetime. or note 'DATETIME': datetime. column. This is useful for applications that need to store birthdates for which you may not know the exact date. In this case, you simply store the date as ''2009-00-00'' or ''2009-01-00''. However, with dates such as these, you should not expect to get correct results for functions such as 'DATE_SUB()' or 'DATE_ADD()' that require complete dates. To disallow zero month or day parts in dates, enable the 'NO_ZERO_IN_DATE' mode.
MySQL permits you to store a 'zero' value of ''0000-00-00'' as a 'dummy date.' In some cases, this is more convenient than using 'NULL' values, and uses less data and index space. To disallow ''0000-00-00'', enable the 'NO_ZERO_DATE' mode.
'Zero' date or time values used through Connector/ODBC are converted automatically to 'NULL' because ODBC cannot handle such values.
The following table shows the format of the 'zero' value for each type. The 'zero' values are special, but you can store or refer to them explicitly using the values shown in the table. You can also do this using the values ''0'' or '0', which are easier to write. For temporal types that include a date part (note 'DATE': datetime, note 'DATETIME': datetime, and note 'TIMESTAMP': datetime.), use of these values may produce warning or errors. The precise behavior depends on which, if any, of the strict and 'NO_ZERO_DATE' SQL modes are enabled; see note sql-mode::.
Data Type 'Zero' Value
*note 'DATE': datetime.''0000-00-00''
*note 'TIME': time. ''00:00:00''
*note 'DATETIME': datetime.''0000-00-00 00:00:00''
*note 'TIMESTAMP': datetime.''0000-00-00 00:00:00''
*note 'YEAR': year. '0000'
File: manual.info.tmp, Node: date-and-time-type-syntax, Next: datetime, Prev: date-and-time-types, Up: date-and-time-types
The date and time data types for representing temporal values are note 'DATE': datetime, note 'TIME': time, note 'DATETIME': datetime, note 'TIMESTAMP': datetime, and *note 'YEAR': year.
For the note 'DATE': datetime. and note 'DATETIME': datetime. range descriptions, 'supported' means that although earlier values might work, there is no guarantee.
MySQL permits fractional seconds for note 'TIME': time, note 'DATETIME': datetime, and note 'TIMESTAMP': datetime. values, with up to microseconds (6 digits) precision. To define a column that includes a fractional seconds part, use the syntax 'TYPE_NAME(FSP)', where TYPE_NAME is note 'TIME': time, note 'DATETIME': datetime, or note 'TIMESTAMP': datetime, and FSP is the fractional seconds precision. For example:
CREATE TABLE t1 (t TIME(3), dt DATETIME(6), ts TIMESTAMP(0));The FSP value, if given, must be in the range 0 to 6. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. (This differs from the standard SQL default of 6, for compatibility with previous MySQL versions.)
Any note 'TIMESTAMP': datetime. or note 'DATETIME': datetime. column in a table can have automatic initialization and updating properties; see *note timestamp-initialization::.
*note 'DATE': datetime.
A date. The supported range is ''1000-01-01'' to ''9999-12-31''. MySQL displays note 'DATE': datetime. values in ''YYYY-MM-DD'' format, but permits assignment of values to note 'DATE': datetime. columns using either strings or numbers.
*note 'DATETIME[(FSP)]': datetime.
A date and time combination. The supported range is ''1000-01-01 00:00:00.000000'' to ''9999-12-31 23:59:59.499999''. MySQL displays note 'DATETIME': datetime. values in ''YYYY-MM-DD HH:MM:SS[.FRACTION]'' format, but permits assignment of values to note 'DATETIME': datetime. columns using either strings or numbers.
An optional FSP value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision isAutomatic initialization and updating to the current date and time for note 'DATETIME': datetime. columns can be specified using 'DEFAULT' and 'ON UPDATE' column definition clauses, as described in note timestamp-initialization::.
*note 'TIMESTAMP[(FSP)]': datetime.
A timestamp. The range is ''1970-01-01 00:00:01.000000'' UTC to ''2038-01-19 03:14:07.499999'' UTC. note 'TIMESTAMP': datetime. values are stored as the number of seconds since the epoch (''1970-01-01 00:00:00'' UTC). A note 'TIMESTAMP': datetime. cannot represent the value ''1970-01-01 00:00:00'' because that is equivalent to 0 seconds from the epoch and the value 0 is reserved for representing ''0000-00-00 00:00:00'', the 'zero' *note 'TIMESTAMP': datetime. value.
An optional FSP value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision isThe way the server handles 'TIMESTAMP' definitions depends on the value of the 'explicit_defaults_for_timestamp' system variable (see *note server-system-variables::).
If 'explicit_defaults_for_timestamp' is enabled, there is no automatic assignment of the 'DEFAULT CURRENT_TIMESTAMP' or 'ON UPDATE CURRENT_TIMESTAMP' attributes to any note 'TIMESTAMP': datetime. column. They must be included explicitly in the column definition. Also, any note 'TIMESTAMP': datetime. not explicitly declared as 'NOT NULL' permits 'NULL' values.
If 'explicit_defaults_for_timestamp' is disabled, the server handles 'TIMESTAMP' as follows:
Unless specified otherwise, the first note 'TIMESTAMP': datetime. column in a table is defined to be automatically set to the date and time of the most recent modification if not explicitly assigned a value. This makes note 'TIMESTAMP': datetime. useful for recording the timestamp of an note 'INSERT': insert. or note 'UPDATE': update. operation. You can also set any *note 'TIMESTAMP': datetime. column to the current date and time by assigning it a 'NULL' value, unless it has been defined with the 'NULL' attribute to permit 'NULL' values.
Automatic initialization and updating to the current date and time can be specified using 'DEFAULT CURRENT_TIMESTAMP' and 'ON UPDATE CURRENT_TIMESTAMP' column definition clauses. By default, the first note 'TIMESTAMP': datetime. column has these properties, as previously noted. However, any note 'TIMESTAMP': datetime. column in a table can be defined to have these properties.
*note 'TIME[(FSP)]': time.
A time. The range is ''-838:59:59.000000'' to ''838:59:59.000000''. MySQL displays note 'TIME': time. values in ''HH:MM:SS[.FRACTION]'' format, but permits assignment of values to note 'TIME': time. columns using either strings or numbers.
An optional FSP value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision is*note 'YEAR[(4)]': year.
A year in 4-digit format. MySQL displays note 'YEAR': year. values in YYYY format, but permits assignment of values to note 'YEAR': year. columns using either strings or numbers. Values display as '1901' to '2155', or '0000'.
Note:
The note 'YEAR(2)': year. data type is deprecated and support for it is removed in MySQL 5.7.5. To convert 2-digit note 'YEAR(2)': year. columns to 4-digit note 'YEAR': year. columns, see note migrating-from-year2::.
For additional information about note 'YEAR': year. display format and interpretation of input values, see note year::.
The 'SUM()' and 'AVG()' aggregate functions do not work with temporal values. (They convert the values to numbers, losing everything after the first nonnumeric character.) To work around this problem, convert to numeric units, perform the aggregate operation, and convert back to a temporal value. Examples:
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(TIME_COL))) FROM TBL_NAME;
SELECT FROM_DAYS(SUM(TO_DAYS(DATE_COL))) FROM TBL_NAME;Note:
The MySQL server can be run with the 'MAXDB' SQL mode enabled. In this case, note 'TIMESTAMP': datetime. is identical with note 'DATETIME': datetime. If this mode is enabled at the time that a table is created, note 'TIMESTAMP': datetime. columns are created as note 'DATETIME': datetime. columns. As a result, such columns use note 'DATETIME': datetime. display format, have the same range of values, and there is no automatic initialization or updating to the current date and time. See note sql-mode::.
Note:
As of MySQL 5.7.22, 'MAXDB' is deprecated; expect it to removed in a future version of MySQL.
File: manual.info.tmp, Node: datetime, Next: time, Prev: date-and-time-type-syntax, Up: date-and-time-types
The 'DATE', 'DATETIME', and 'TIMESTAMP' types are related. This section describes their characteristics, how they are similar, and how they differ. MySQL recognizes 'DATE', 'DATETIME', and 'TIMESTAMP' values in several formats, described in *note date-and-time-literals::. For the 'DATE' and 'DATETIME' range descriptions, 'supported' means that although earlier values might work, there is no guarantee.
The 'DATE' type is used for values with a date part but no time part. MySQL retrieves and displays 'DATE' values in ''YYYY-MM-DD'' format. The supported range is ''1000-01-01'' to ''9999-12-31''.
The 'DATETIME' type is used for values that contain both date and time parts. MySQL retrieves and displays 'DATETIME' values in ''YYYY-MM-DD HH:MM:SS'' format. The supported range is ''1000-01-01 00:00:00'' to ''9999-12-31 23:59:59''.
The 'TIMESTAMP' data type is used for values that contain both date and time parts. 'TIMESTAMP' has a range of ''1970-01-01 00:00:01'' UTC to ''2038-01-19 03:14:07'' UTC.
A 'DATETIME' or 'TIMESTAMP' value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. In particular, any fractional part in a value inserted into a 'DATETIME' or 'TIMESTAMP' column is stored rather than discarded. With the fractional part included, the format for these values is ''YYYY-MM-DD HH:MM:SS[.FRACTION]'', the range for 'DATETIME' values is ''1000-01-01 00:00:00.000000'' to ''9999-12-31 23:59:59.499999'', and the range for 'TIMESTAMP' values is ''1970-01-01 00:00:01.000000'' to ''2038-01-19 03:14:07.499999''. The fractional part should always be separated from the rest of the time by a decimal point; no other fractional seconds delimiter is recognized. For information about fractional seconds support in MySQL, see *note fractional-seconds::.
The 'TIMESTAMP' and 'DATETIME' data types offer automatic initialization and updating to the current date and time. For more information, see *note timestamp-initialization::.
MySQL converts 'TIMESTAMP' values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as 'DATETIME'.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a 'TIMESTAMP' value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the 'time_zone' system variable. For more information, see *note time-zone-support::.
Invalid 'DATE', 'DATETIME', or 'TIMESTAMP' values are converted to the 'zero' value of the appropriate type (''0000-00-00'' or ''0000-00-00 00:00:00''), if the SQL mode permits this conversion. The precise behavior depends on which if any of strict SQL mode and the 'NO_ZERO_DATE' SQL mode are enabled; see *note sql-mode::.
Be aware of certain properties of date value interpretation in MySQL:
MySQL permits a 'relaxed' format for values specified as strings, in which any punctuation character may be used as the delimiter between date parts or time parts. In some cases, this syntax can be deceiving. For example, a value such as ''10:11:12'' might look like a time value because of the ':', but is interpreted as the year ''2010-11-12'' if used in date context. The value ''10:45:15'' is converted to ''0000-00-00'' because ''45'' is not a valid month.
The only delimiter recognized between a date and time part and a fractional seconds part is the decimal point.
The server requires that month and day values be valid, and not merely in the range 1 to 12 and 1 to 31, respectively. With strict mode disabled, invalid dates such as ''2004-04-31'' are converted to ''0000-00-00'' and a warning is generated. With strict mode enabled, invalid dates generate an error. To permit such dates, enable 'ALLOW_INVALID_DATES'. See *note sql-mode::, for more information.
MySQL does not accept 'TIMESTAMP' values that include a zero in the day or month column or values that are not a valid date. The sole exception to this rule is the special 'zero' value ''0000-00-00 00:00:00'', if the SQL mode permits this value. The precise behavior depends on which if any of strict SQL mode and the 'NO_ZERO_DATE' SQL mode are enabled; see *note sql-mode::.
Dates containing 2-digit year values are ambiguous because the century is unknown. MySQL interprets 2-digit year values using these rules:
* Year values in the range '00-69' become '2000-2069'.
* Year values in the range '70-99' become '1970-1999'.See also *note two-digit-years::.
Note:
The MySQL server can be run with the 'MAXDB' SQL mode enabled. In this case, 'TIMESTAMP' is identical with 'DATETIME'. If this mode is enabled at the time that a table is created, 'TIMESTAMP' columns are created as 'DATETIME' columns. As a result, such columns use 'DATETIME' display format, have the same range of values, and there is no automatic initialization or updating to the current date and time. See *note sql-mode::.
Note:
As of MySQL 5.7.22, 'MAXDB' is deprecated; expect it to removed in a future version of MySQL.
File: manual.info.tmp, Node: time, Next: year, Prev: datetime, Up: date-and-time-types
MySQL retrieves and displays 'TIME' values in 'HH:MM:SS' format (or 'HHH:MM:SS' format for large hours values). 'TIME' values may range from ''-838:59:59'' to ''838:59:59''. The hours part may be so large because the 'TIME' type can be used not only to represent a time of day (which must be less than 24 hours), but also elapsed time or a time interval between two events (which may be much greater than 24 hours, or even negative).
MySQL recognizes 'TIME' values in several formats, some of which can include a trailing fractional seconds part in up to microseconds (6 digits) precision. See note date-and-time-literals::. For information about fractional seconds support in MySQL, see note fractional-seconds::. In particular, any fractional part in a value inserted into a 'TIME' column is stored rather than discarded. With the fractional part included, the range for 'TIME' values is ''-838:59:59.000000'' to ''838:59:59.000000''.
Be careful about assigning abbreviated values to a 'TIME' column. MySQL interprets abbreviated 'TIME' values with colons as time of the day. That is, ''11:12'' means ''11:12:00'', not ''00:11:12''. MySQL interprets abbreviated values without colons using the assumption that the two rightmost digits represent seconds (that is, as elapsed time rather than as time of day). For example, you might think of ''1112'' and '1112' as meaning ''11:12:00'' (12 minutes after 11 o'clock), but MySQL interprets them as ''00:11:12'' (11 minutes, 12 seconds). Similarly, ''12'' and '12' are interpreted as ''00:00:12''.
The only delimiter recognized between a time part and a fractional seconds part is the decimal point.
By default, values that lie outside the 'TIME' range but are otherwise valid are clipped to the closest endpoint of the range. For example, ''-850:00:00'' and ''850:00:00'' are converted to ''-838:59:59'' and ''838:59:59''. Invalid 'TIME' values are converted to ''00:00:00''. Note that because ''00:00:00'' is itself a valid 'TIME' value, there is no way to tell, from a value of ''00:00:00'' stored in a table, whether the original value was specified as ''00:00:00'' or whether it was invalid.
For more restrictive treatment of invalid 'TIME' values, enable strict SQL mode to cause errors to occur. See *note sql-mode::.
File: manual.info.tmp, Node: year, Next: migrating-from-year2, Prev: time, Up: date-and-time-types
The 'YEAR' type is a 1-byte type used to represent year values. It can be declared as 'YEAR' with an implicit display width of 4 characters, or equivalently as 'YEAR(4)' with an explicit display width.
Note:
The 2-digit 'YEAR(2)' data type is deprecated and support for it is removed in MySQL 5.7.5. To convert 2-digit 'YEAR(2)' columns to 4-digit 'YEAR' columns, see *note migrating-from-year2::.
MySQL displays 'YEAR' values in YYYY format, with a range of '1901' to '2155', and '0000'.
'YEAR' accepts input values in a variety of formats:
As 4-digit strings in the range ''1901'' to ''2155''.
As 4-digit numbers in the range '1901' to '2155'.
As 1- or 2-digit strings in the range ''0'' to ''99''. MySQL converts values in the ranges ''0'' to ''69'' and ''70'' to ''99'' to 'YEAR' values in the ranges '2000' to '2069' and '1970' to '1999'.
As 1- or 2-digit numbers in the range '0' to '99'. MySQL converts values in the ranges '1' to '69' and '70' to '99' to 'YEAR' values in the ranges '2001' to '2069' and '1970' to '1999'.
The result of inserting a numeric '0' has a display value of '0000' and an internal value of '0000'. To insert zero and have it be interpreted as '2000', specify it as a string ''0'' or ''00''.
As the result of functions that return a value that is acceptable in 'YEAR' context, such as 'NOW()'.
If strict SQL mode is not enabled, MySQL converts invalid 'YEAR' values to '0000'. In strict SQL mode, attempting to insert an invalid 'YEAR' value produces an error.
See also *note two-digit-years::.
File: manual.info.tmp, Node: migrating-from-year2, Next: timestamp-initialization, Prev: year, Up: date-and-time-types
This section describes problems that can occur when using the 2-digit note 'YEAR(2)': year. data type and provides information about converting existing note 'YEAR(2)': year. columns to 4-digit year-valued columns, which can be declared as 'YEAR' with an implicit display width of 4 characters, or equivalently as 'YEAR(4)' with an explicit display width.
Although the internal range of values for 'YEAR'/note 'YEAR(4)': year. and the deprecated note 'YEAR(2)': year. type is the same ('1901' to '2155', and '0000'), the display width for note 'YEAR(2)': year. makes that type inherently ambiguous because displayed values indicate only the last two digits of the internal values and omit the century digits. The result can be a loss of information under certain circumstances. For this reason, avoid using note 'YEAR(2)': year. in your applications and use 'YEAR'/note 'YEAR(4)': year. wherever you need a year-valued data type. As of MySQL 5.7.5, support for note 'YEAR(2)': year. is removed and existing 2-digit note 'YEAR(2)': year. columns must be converted to 4-digit note 'YEAR': year. columns to become usable again.
YEAR(2) Limitations
Issues with the *note 'YEAR(2)': year. data type include ambiguity of displayed values, and possible loss of information when values are dumped and reloaded or converted to strings.
Displayed note 'YEAR(2)': year. values can be ambiguous. It is possible for up to three note 'YEAR(2)': year. values that have different internal values to have the same displayed value, as the following example demonstrates:
mysql> CREATE TABLE t (y2 YEAR(2), y4 YEAR);
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> INSERT INTO t (y2) VALUES(1912),(2012),(2112);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> UPDATE t SET y4 = y2;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> SELECT * FROM t;
+------+------+
| y2 | y4 |
+------+------+
| 12 | 1912 |
| 12 | 2012 |
| 12 | 2112 |
+------+------+
3 rows in set (0.00 sec)If you use *note 'mysqldump': mysqldump. to dump the table created in the preceding example, the dump file represents all 'y2' values using the same 2-digit representation ('12'). If you reload the table from the dump file, all resulting rows have internal value '2012' and display value '12', thus losing the distinctions between them.
Conversion of a 2-digit or 4-digit note 'YEAR': year. data value to string form uses the data type display width. Suppose that a note 'YEAR(2)': year. column and a 'YEAR'/note 'YEAR(4)': year. column both contain the value '1970'. Assigning each column to a string results in a value of ''70'' or ''1970'', respectively. That is, loss of information occurs for conversion from note 'YEAR(2)': year. to string.
Values outside the range from '1970' to '2069' are stored incorrectly when inserted into a note 'YEAR(2)': year. column in a note 'CSV': csv-storage-engine. table. For example, inserting '2211' results in a display value of '11' but an internal value of '2011'.
To avoid these problems, use the 4-digit note 'YEAR': year. or note 'YEAR(4)': year. data type rather than the 2-digit *note 'YEAR(2)': year. data type. Suggestions regarding migration strategies appear later in this section.
Reduced/Removed YEAR(2) Support in MySQL 5.7
Before MySQL 5.7.5, support for note 'YEAR(2)': year. is diminished. As of MySQL 5.7.5, support for note 'YEAR(2)': year. is removed.
*note 'YEAR(2)': year. column definitions for new tables produce warnings or errors:
* Before MySQL 5.7.5, *note 'YEAR(2)': year. column definitions
for new tables are converted (with an
'ER_INVALID_YEAR_COLUMN_LENGTH'
(https://dev.mysql.com/doc/mysql-errors/5.7/en/server-error-reference.html#error_er_invalid_year_column_length)
warning) to 4-digit *note 'YEAR': year. columns:
mysql> CREATE TABLE t1 (y YEAR(2));
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Warning
Code: 1818
Message: YEAR(2) column type is deprecated. Creating YEAR(4) column instead.
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`y` year(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
* As of MySQL 5.7.5, *note 'YEAR(2)': year. column definitions
for new tables produce an 'ER_INVALID_YEAR_COLUMN_LENGTH'
(https://dev.mysql.com/doc/mysql-errors/5.7/en/server-error-reference.html#error_er_invalid_year_column_length)
error:
mysql> CREATE TABLE t1 (y YEAR(2));
ERROR 1818 (HY000): Supports only YEAR or YEAR(4) column.note 'YEAR(2)': year. column in existing tables remain as note 'YEAR(2)': year.:
* Before MySQL 5.7.5, *note 'YEAR(2)': year. is processed in
queries as in older versions of MySQL.
* As of MySQL 5.7.5, *note 'YEAR(2)': year. columns in queries
produce warnings or errors.Several programs or statements convert note 'YEAR(2)': year. columns to 4-digit note 'YEAR': year. columns automatically:
* *note 'ALTER TABLE': alter-table. statements that result in a
table rebuild.
* *note 'REPAIR TABLE': repair-table. (which *note 'CHECK
TABLE': check-table. recommends you use, if it finds a table
that contains *note 'YEAR(2)': year. columns).
* *note 'mysql_upgrade': mysql-upgrade. (which uses *note
'REPAIR TABLE': repair-table.).
* Dumping with *note 'mysqldump': mysqldump. and reloading the
dump file. Unlike the conversions performed by the preceding
three items, a dump and reload has the potential to change
data values.A MySQL upgrade usually involves at least one of the last two items. However, with respect to note 'YEAR(2)': year, note 'mysql_upgrade': mysql-upgrade. is preferable to *note 'mysqldump': mysqldump, which, as noted, can change data values.
Migrating from YEAR(2) to 4-Digit YEAR
To convert 2-digit note 'YEAR(2)': year. columns to 4-digit note 'YEAR': year. columns, you can do so manually at any time without upgrading. Alternatively, you can upgrade to a version of MySQL with reduced or removed support for note 'YEAR(2)': year. (MySQL 5.6.6 or later), then have MySQL convert note 'YEAR(2)': year. columns automatically. In the latter case, avoid upgrading by dumping and reloading your data because that can change data values. In addition, if you use replication, there are upgrade considerations you must take into account.
To convert 2-digit note 'YEAR(2)': year. columns to 4-digit note 'YEAR': year. manually, use note 'ALTER TABLE': alter-table. or note 'REPAIR TABLE': repair-table. Suppose that a table 't1' has this definition:
CREATE TABLE t1 (ycol YEAR(2) NOT NULL DEFAULT '70');Modify the column using 'ALTER TABLE' as follows:
ALTER TABLE t1 FORCE;The note 'ALTER TABLE': alter-table. statement converts the table without changing note 'YEAR(2)': year. values. If the server is a replication source, the *note 'ALTER TABLE': alter-table. statement replicates to replicas and makes the corresponding table change on each one.
Another migration method is to perform a binary upgrade: Upgrade MySQL in place without dumping and reloading your data. Then run note 'mysql_upgrade': mysql-upgrade, which uses note 'REPAIR TABLE': repair-table. to convert 2-digit note 'YEAR(2)': year. columns to 4-digit note 'YEAR': year. columns without changing data values. If the server is a replication source, the note 'REPAIR TABLE': repair-table. statements replicate to replicas and make the corresponding table changes on each one, unless you invoke note 'mysql_upgrade': mysql-upgrade. with the '--skip-write-binlog' option.
Upgrades to replication servers usually involve upgrading replicas to a newer version of MySQL, then upgrading the source. For example, if a source and replica both run MySQL 5.5, a typical upgrade sequence involves upgrading the replica to 5.6, then upgrading the source to 5.6. With regard to the different treatment of note 'YEAR(2)': year. as of MySQL 5.6.6, that upgrade sequence results in a problem: Suppose that the replica has been upgraded but not yet the source. Then creating a table containing a 2-digit note 'YEAR(2)': year. column on the source results in a table containing a 4-digit *note 'YEAR': year. column on the replica. Consequently, the following operations have a different result on the source and replica, if you use statement-based replication:
Inserting numeric '0'. The resulting value has an internal value of '2000' on the source but '0000' on the replica.
Converting note 'YEAR(2)': year. to string. This operation uses the display value of note 'YEAR(2)': year. on the source but *note 'YEAR(4)': year. on the replica.
To avoid such problems, modify all 2-digit note 'YEAR(2)': year. columns on the source to 4-digit note 'YEAR': year. columns before upgrading. (Use note 'ALTER TABLE': alter-table, as described previously.) That makes it possible to upgrade normally (replica first, then source) without introducing any note 'YEAR(2)': year. to *note 'YEAR(4)': year. differences between the source and replica.
One migration method should be avoided: Do not dump your data with note 'mysqldump': mysqldump. and reload the dump file after upgrading. That has the potential to change note 'YEAR(2)': year. values, as described previously.
A migration from 2-digit note 'YEAR(2)': year. columns to 4-digit note 'YEAR': year. columns should also involve examining application code for the possibility of changed behavior under conditions such as these:
Code that expects selecting a *note 'YEAR': year. column to produce exactly two digits.
Code that does not account for different handling for inserts of numeric '0': Inserting '0' into note 'YEAR(2)': year. or note 'YEAR(4)': year. results in an internal value of '2000' or '0000', respectively.
File: manual.info.tmp, Node: timestamp-initialization, Next: fractional-seconds, Prev: migrating-from-year2, Up: date-and-time-types
note 'TIMESTAMP': datetime. and note 'DATETIME': datetime. columns can be automatically initializated and updated to the current date and time (that is, the current timestamp).
For any note 'TIMESTAMP': datetime. or note 'DATETIME': datetime. column in a table, you can assign the current timestamp as the default value, the auto-update value, or both:
An auto-initialized column is set to the current timestamp for inserted rows that specify no value for the column.
An auto-updated column is automatically updated to the current timestamp when the value of any other column in the row is changed from its current value. An auto-updated column remains unchanged if all other columns are set to their current values. To prevent an auto-updated column from updating when other columns change, explicitly set it to its current value. To update an auto-updated column even when other columns do not change, explicitly set it to the value it should have (for example, set it to 'CURRENT_TIMESTAMP').
In addition, if the 'explicit_defaults_for_timestamp' system variable is disabled, you can initialize or update any *note 'TIMESTAMP': datetime. (but not 'DATETIME') column to the current date and time by assigning it a 'NULL' value, unless it has been defined with the 'NULL' attribute to permit 'NULL' values.
To specify automatic properties, use the 'DEFAULT CURRENT_TIMESTAMP' and 'ON UPDATE CURRENT_TIMESTAMP' clauses in column definitions. The order of the clauses does not matter. If both are present in a column definition, either can occur first. Any of the synonyms for 'CURRENT_TIMESTAMP' have the same meaning as 'CURRENT_TIMESTAMP'. These are 'CURRENT_TIMESTAMP()', 'NOW()', 'LOCALTIME', 'LOCALTIME()', 'LOCALTIMESTAMP', and 'LOCALTIMESTAMP()'.
Use of 'DEFAULT CURRENT_TIMESTAMP' and 'ON UPDATE CURRENT_TIMESTAMP' is specific to note 'TIMESTAMP': datetime. and note 'DATETIME': datetime. The 'DEFAULT' clause also can be used to specify a constant (nonautomatic) default value (for example, 'DEFAULT 0' or 'DEFAULT '2000-01-01 00:00:00'').
Note:
The following examples use 'DEFAULT 0', a default that can produce warnings or errors depending on whether strict SQL mode or the 'NO_ZERO_DATE' SQL mode is enabled. Be aware that the 'TRADITIONAL' SQL mode includes strict mode and 'NO_ZERO_DATE'. See *note sql-mode::.
note 'TIMESTAMP': datetime. or note 'DATETIME': datetime. column definitions can specify the current timestamp for both the default and auto-update values, for one but not the other, or for neither. Different columns can have different combinations of automatic properties. The following rules describe the possibilities:
With both 'DEFAULT CURRENT_TIMESTAMP' and 'ON UPDATE CURRENT_TIMESTAMP', the column has the current timestamp for its default value and is automatically updated to the current timestamp.
CREATE TABLE t1 (
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);With a 'DEFAULT' clause but no 'ON UPDATE CURRENT_TIMESTAMP' clause, the column has the given default value and is not automatically updated to the current timestamp.
The default depends on whether the 'DEFAULT' clause specifies 'CURRENT_TIMESTAMP' or a constant value. With 'CURRENT_TIMESTAMP', the default is the current timestamp.
CREATE TABLE t1 (
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
dt DATETIME DEFAULT CURRENT_TIMESTAMP
);With a constant, the default is the given value. In this case, the column has no automatic properties at all.
CREATE TABLE t1 (
ts TIMESTAMP DEFAULT 0,
dt DATETIME DEFAULT 0
);With an 'ON UPDATE CURRENT_TIMESTAMP' clause and a constant 'DEFAULT' clause, the column is automatically updated to the current timestamp and has the given constant default value.
CREATE TABLE t1 (
ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP,
dt DATETIME DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
);With an 'ON UPDATE CURRENT_TIMESTAMP' clause but no 'DEFAULT' clause, the column is automatically updated to the current timestamp but does not have the current timestamp for its default value.
The default in this case is type dependent. *note 'TIMESTAMP': datetime. has a default of 0 unless defined with the 'NULL' attribute, in which case the default is 'NULL'.
CREATE TABLE t1 (
ts1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- default 0
ts2 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL
);*note 'DATETIME': datetime. has a default of 'NULL' unless defined with the 'NOT NULL' attribute, in which case the default is 0.
CREATE TABLE t1 (
dt1 DATETIME ON UPDATE CURRENT_TIMESTAMP, -- default NULL
dt2 DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP -- default 0
);note 'TIMESTAMP': datetime. and note 'DATETIME': datetime. columns have no automatic properties unless they are specified explicitly, with this exception: If the 'explicit_defaults_for_timestamp' system variable is disabled, the first note 'TIMESTAMP': datetime. column has both 'DEFAULT CURRENT_TIMESTAMP' and 'ON UPDATE CURRENT_TIMESTAMP' if neither is specified explicitly. To suppress automatic properties for the first note 'TIMESTAMP': datetime. column, use one of these strategies:
Enable the 'explicit_defaults_for_timestamp' system variable. In this case, the 'DEFAULT CURRENT_TIMESTAMP' and 'ON UPDATE CURRENT_TIMESTAMP' clauses that specify automatic initialization and updating are available, but are not assigned to any *note 'TIMESTAMP': datetime. column unless explicitly included in the column definition.
Alternatively, if 'explicit_defaults_for_timestamp' is disabled, do either of the following:
* Define the column with a 'DEFAULT' clause that specifies a
constant default value.
* Specify the 'NULL' attribute. This also causes the column to
permit 'NULL' values, which means that you cannot assign the
current timestamp by setting the column to 'NULL'. Assigning
'NULL' sets the column to 'NULL', not the current timestamp.
To assign the current timestamp, set the column to
'CURRENT_TIMESTAMP' or a synonym such as 'NOW()'.Consider these table definitions:
CREATE TABLE t1 (
ts1 TIMESTAMP DEFAULT 0,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t2 (
ts1 TIMESTAMP NULL,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t3 (
ts1 TIMESTAMP NULL DEFAULT 0,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);The tables have these properties:
In each table definition, the first *note 'TIMESTAMP': datetime. column has no automatic initialization or updating.
The tables differ in how the 'ts1' column handles 'NULL' values. For 't1', 'ts1' is 'NOT NULL' and assigning it a value of 'NULL' sets it to the current timestamp. For 't2' and 't3', 'ts1' permits 'NULL' and assigning it a value of 'NULL' sets it to 'NULL'.
't2' and 't3' differ in the default value for 'ts1'. For 't2', 'ts1' is defined to permit 'NULL', so the default is also 'NULL' in the absence of an explicit 'DEFAULT' clause. For 't3', 'ts1' permits 'NULL' but has an explicit default of 0.
If a note 'TIMESTAMP': datetime. or note 'DATETIME': datetime. column definition includes an explicit fractional seconds precision value anywhere, the same value must be used throughout the column definition. This is permitted:
CREATE TABLE t1 (
ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
);This is not permitted:
CREATE TABLE t1 (
ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(3)
);TIMESTAMP Initialization and the NULL Attribute
If the 'explicit_defaults_for_timestamp' system variable is disabled, note 'TIMESTAMP': datetime. columns by default are 'NOT NULL', cannot contain 'NULL' values, and assigning 'NULL' assigns the current timestamp. To permit a note 'TIMESTAMP': datetime. column to contain 'NULL', explicitly declare it with the 'NULL' attribute. In this case, the default value also becomes 'NULL' unless overridden with a 'DEFAULT' clause that specifies a different default value. 'DEFAULT NULL' can be used to explicitly specify 'NULL' as the default value. (For a note 'TIMESTAMP': datetime. column not declared with the 'NULL' attribute, 'DEFAULT NULL' is invalid.) If a note 'TIMESTAMP': datetime. column permits 'NULL' values, assigning 'NULL' sets it to 'NULL', not to the current timestamp.
The following table contains several *note 'TIMESTAMP': datetime. columns that permit 'NULL' values:
CREATE TABLE t
(
ts1 TIMESTAMP NULL DEFAULT NULL,
ts2 TIMESTAMP NULL DEFAULT 0,
ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
);A *note 'TIMESTAMP': datetime. column that permits 'NULL' values does not take on the current timestamp at insert time except under one of the following conditions:
Its default value is defined as 'CURRENT_TIMESTAMP' and no value is specified for the column
'CURRENT_TIMESTAMP' or any of its synonyms such as 'NOW()' is explicitly inserted into the column
In other words, a *note 'TIMESTAMP': datetime. column defined to permit 'NULL' values auto-initializes only if its definition includes 'DEFAULT CURRENT_TIMESTAMP':
CREATE TABLE t (ts TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);If the *note 'TIMESTAMP': datetime. column permits 'NULL' values but its definition does not include 'DEFAULT CURRENT_TIMESTAMP', you must explicitly insert a value corresponding to the current date and time. Suppose that tables 't1' and 't2' have these definitions:
CREATE TABLE t1 (ts TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00');
CREATE TABLE t2 (ts TIMESTAMP NULL DEFAULT NULL);To set the *note 'TIMESTAMP': datetime. column in either table to the current timestamp at insert time, explicitly assign it that value. For example:
INSERT INTO t2 VALUES (CURRENT_TIMESTAMP);
INSERT INTO t1 VALUES (NOW());If the 'explicit_defaults_for_timestamp' system variable is enabled, note 'TIMESTAMP': datetime. columns permit 'NULL' values only if declared with the 'NULL' attribute. Also, note 'TIMESTAMP': datetime. columns do not permit assigning 'NULL' to assign the current timestamp, whether declared with the 'NULL' or 'NOT NULL' attribute. To assign the current timestamp, set the column to 'CURRENT_TIMESTAMP' or a synonym such as 'NOW()'.
File: manual.info.tmp, Node: fractional-seconds, Next: mysql-calendar, Prev: timestamp-initialization, Up: date-and-time-types
MySQL has fractional seconds support for note 'TIME': time, note 'DATETIME': datetime, and *note 'TIMESTAMP': datetime. values, with up to microseconds (6 digits) precision:
To define a column that includes a fractional seconds part, use the syntax 'TYPE_NAME(FSP)', where TYPE_NAME is note 'TIME': time, note 'DATETIME': datetime, or *note 'TIMESTAMP': datetime, and FSP is the fractional seconds precision. For example:
CREATE TABLE t1 (t TIME(3), dt DATETIME(6));The FSP value, if given, must be in the range 0 to 6. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. (This differs from the standard SQL default of 6, for compatibility with previous MySQL versions.)
Inserting a note 'TIME': time, note 'DATE': datetime, or *note 'TIMESTAMP': datetime. value with a fractional seconds part into a column of the same type but having fewer fractional digits results in rounding. Consider a table created and populated as follows:
CREATE TABLE fractest( c1 TIME(2), c2 DATETIME(2), c3 TIMESTAMP(2) );
INSERT INTO fractest VALUES
('17:51:04.777', '2018-09-08 17:51:04.777', '2018-09-08 17:51:04.777');The temporal values are inserted into the table with rounding:
mysql> SELECT * FROM fractest;
+-------------+------------------------+------------------------+
| c1 | c2 | c3 |
+-------------+------------------------+------------------------+
| 17:51:04.78 | 2018-09-08 17:51:04.78 | 2018-09-08 17:51:04.78 |
+-------------+------------------------+------------------------+No warning or error is given when such rounding occurs. This behavior follows the SQL standard, and is not affected by the server 'sql_mode' setting.
Functions that take temporal arguments accept values with fractional seconds. Return values from temporal functions include fractional seconds as appropriate. For example, 'NOW()' with no argument returns the current date and time with no fractional part, but takes an optional argument from 0 to 6 to specify that the return value includes a fractional seconds part of that many digits.
Syntax for temporal literals produces temporal values: 'DATE 'STR'', 'TIME 'STR'', and 'TIMESTAMP 'STR'', and the ODBC-syntax equivalents. The resulting value includes a trailing fractional seconds part if specified. Previously, the temporal type keyword was ignored and these constructs produced the string value. See *note date-and-time-standard-sql-literals::
File: manual.info.tmp, Node: mysql-calendar, Next: date-and-time-type-conversion, Prev: fractional-seconds, Up: date-and-time-types
MySQL uses what is known as a proleptic Gregorian calendar.
Every country that has switched from the Julian to the Gregorian calendar has had to discard at least ten days during the switch. To see how this works, consider the month of October 1582, when the first Julian-to-Gregorian switch occurred.
Monday Tuesday Wednesday Thursday Friday Saturday Sunday
1
2 3 4 15 16 17
18
19 20 21 22 23 24
25
26 27 28 29 30 31
There are no dates between October 4 and October 15. This discontinuity is called the cutover. Any dates before the cutover are Julian, and any dates following the cutover are Gregorian. Dates during a cutover are nonexistent.
A calendar applied to dates when it was not actually in use is called proleptic. Thus, if we assume there was never a cutover and Gregorian rules always rule, we have a proleptic Gregorian calendar. This is what is used by MySQL, as is required by standard SQL. For this reason, dates prior to the cutover stored as MySQL note 'DATE': datetime. or note 'DATETIME': datetime. values must be adjusted to compensate for the difference. It is important to realize that the cutover did not occur at the same time in all countries, and that the later it happened, the more days were lost. For example, in Great Britain, it took place in 1752, when Wednesday September 2 was followed by Thursday September 14. Russia remained on the Julian calendar until 1918, losing 13 days in the process, and what is popularly referred to as its 'October Revolution' occurred in November according to the Gregorian calendar.
File: manual.info.tmp, Node: date-and-time-type-conversion, Next: two-digit-years, Prev: mysql-calendar, Up: date-and-time-types
To some extent, you can convert a value from one temporal type to another. However, there may be some alteration of the value or loss of information. In all cases, conversion between temporal types is subject to the range of valid values for the resulting type. For example, although note 'DATE': datetime, note 'DATETIME': datetime, and note 'TIMESTAMP': datetime. values all can be specified using the same set of formats, the types do not all have the same range of values. note 'TIMESTAMP': datetime. values cannot be earlier than '1970' UTC or later than ''2038-01-19 03:14:07'' UTC. This means that a date such as ''1968-01-01'', while valid as a note 'DATE': datetime. or note 'DATETIME': datetime. value, is not valid as a *note 'TIMESTAMP': datetime. value and is converted to '0'.
Conversion of *note 'DATE': datetime. values:
Conversion to a note 'DATETIME': datetime. or note 'TIMESTAMP': datetime. value adds a time part of ''00:00:00'' because the *note 'DATE': datetime. value contains no time information.
Conversion to a *note 'TIME': time. value is not useful; the result is ''00:00:00''.
Conversion of note 'DATETIME': datetime. and note 'TIMESTAMP': datetime. values:
Conversion to a *note 'DATE': datetime. value takes fractional seconds into account and rounds the time part. For example, ''1999-12-31 23:59:59.499'' becomes ''1999-12-31'', whereas ''1999-12-31 23:59:59.500'' becomes ''2000-01-01''.
Conversion to a note 'TIME': time. value discards the date part because the note 'TIME': time. type contains no date information.
For conversion of note 'TIME': time. values to other temporal types, the value of 'CURRENT_DATE()' is used for the date part. The note 'TIME': time. is interpreted as elapsed time (not time of day) and added to the date. This means that the date part of the result differs from the current date if the time value is outside the range from ''00:00:00'' to ''23:59:59''.
Suppose that the current date is ''2012-01-01''. note 'TIME': time. values of ''12:00:00'', ''24:00:00'', and ''-12:00:00'', when converted to note 'DATETIME': datetime. or *note 'TIMESTAMP': datetime. values, result in ''2012-01-01 12:00:00'', ''2012-01-02 00:00:00'', and ''2011-12-31 12:00:00'', respectively.
Conversion of note 'TIME': time. to note 'DATE': datetime. is similar but discards the time part from the result: ''2012-01-01'', ''2012-01-02'', and ''2011-12-31'', respectively.
Explicit conversion can be used to override implicit conversion. For example, in comparison of note 'DATE': datetime. and note 'DATETIME': datetime. values, the note 'DATE': datetime. value is coerced to the note 'DATETIME': datetime. type by adding a time part of ''00:00:00''. To perform the comparison by ignoring the time part of the *note 'DATETIME': datetime. value instead, use the 'CAST()' function in the following way:
DATE_COL = CAST(DATETIME_COL AS DATE)Conversion of note 'TIME': time. and note 'DATETIME': datetime. values to numeric form (for example, by adding '+0') depends on whether the value contains a fractional seconds part. note 'TIME(N)': time. or note 'DATETIME(N)': datetime. is converted to integer when N is 0 (or omitted) and to a 'DECIMAL' value with N decimal digits when N is greater than 0:
mysql> SELECT CURTIME(), CURTIME()+0, CURTIME(3)+0;
+-----------+-------------+--------------+
| CURTIME() | CURTIME()+0 | CURTIME(3)+0 |
+-----------+-------------+--------------+
| 09:28:00 | 92800 | 92800.887 |
+-----------+-------------+--------------+
mysql> SELECT NOW(), NOW()+0, NOW(3)+0;
+---------------------+----------------+--------------------+
| NOW() | NOW()+0 | NOW(3)+0 |
+---------------------+----------------+--------------------+
| 2012-08-15 09:28:00 | 20120815092800 | 20120815092800.889 |
+---------------------+----------------+--------------------+File: manual.info.tmp, Node: two-digit-years, Prev: date-and-time-type-conversion, Up: date-and-time-types
Date values with 2-digit years are ambiguous because the century is unknown. Such values must be interpreted into 4-digit form because MySQL stores years internally using 4 digits.
For note 'DATETIME': datetime, note 'DATE': datetime, and *note 'TIMESTAMP': datetime. types, MySQL interprets dates specified with ambiguous year values using these rules:
Year values in the range '00-69' become '2000-2069'.
Year values in the range '70-99' become '1970-1999'.
For 'YEAR', the rules are the same, with this exception: A numeric '00' inserted into 'YEAR' results in '0000' rather than '2000'. To specify zero for 'YEAR' and have it be interpreted as '2000', specify it as a string ''0'' or ''00''.
Remember that these rules are only heuristics that provide reasonable guesses as to what your data values mean. If the rules used by MySQL do not produce the values you require, you must provide unambiguous input containing 4-digit year values.
'ORDER BY' properly sorts *note 'YEAR': year. values that have 2-digit years.
Some functions like 'MIN()' and 'MAX()' convert a note 'YEAR': year. to a number. This means that a value with a 2-digit year does not work properly with these functions. The fix in this case is to convert the note 'YEAR': year. to 4-digit year format.
File: manual.info.tmp, Node: string-types, Next: spatial-types, Prev: date-and-time-types, Up: data-types