Skip navigation links
MySQL 5.6 Reference Manual :: 11 Data Types :: 11.1 Data Type Overview :: 11.1.2 Date and Time Type Overview
11.1.3 String Type Overview »
Section Navigation      [Toggle]

11.1.2. Date and Time Type Overview

A summary of the temporal data types follows. For additional information about properties and storage requirements of the temporal types, see Section 11.3, “Date and Time Types”, and Section 11.5, “Data Type Storage Requirements”. For descriptions of functions that operate on temporal values, see Section 12.7, “Date and Time Functions”.

For the DATE and DATETIME range descriptions, “supported” means that although earlier values might work, there is no guarantee.

MySQL 5.6.4 and up permits fractional seconds for TIME, DATETIME, and TIMESTAMP 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 TIME, DATETIME, or TIMESTAMP, 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.)

MySQL 5.6.5 introduces expanded automatic initialization and updating of temporal types. Any TIMESTAMP column in a table can have these properties, rather than at most one column per table. In addition, these properties are now available for DATETIME columns.

  • DATE

    A date. The supported range is '1000-01-01' to '9999-12-31'. MySQL displays DATE values in 'YYYY-MM-DD' format, but permits assignment of values to DATE columns using either strings or numbers.

  • DATETIME[(fsp)]

    A date and time combination. The supported range is '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'. MySQL displays DATETIME values in 'YYYY-MM-DD HH:MM:SS[.fraction]' format, but permits assignment of values to DATETIME columns using either strings or numbers.

    As of MySQL 5.6.4, 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 0.

    As of MySQL 5.6.5, automatic initialization and updating to the current date and time for DATETIME columns can be specified using DEFAULT and ON UPDATE column definition clauses, as described in Section 11.3.4, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

  • TIMESTAMP[(fsp)]

    A timestamp. The range is '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC. TIMESTAMP values are stored as the number of seconds since the epoch ('1970-01-01 00:00:00' UTC). A TIMESTAMP 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” TIMESTAMP value.

    As of MySQL 5.6.4, 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 0.

    Unless specified otherwise, the first TIMESTAMP 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 TIMESTAMP useful for recording the timestamp of an INSERT or UPDATE operation. You can also set any TIMESTAMP 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 and ON UPDATE column definition clauses. By default, the first TIMESTAMP column has these properties, as previously noted. As of MySQL 5.6.5, any TIMESTAMP column in a table can be defined to have these properties. Before 5.6.5, at most one TIMESTAMP column per table can have them, but it is possible to suppress them for the first column and instead assign them to a different TIMESTAMP column. See Section 11.3.4, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

    Note

    The TIMESTAMP format that was used prior to MySQL 4.1 is not supported in MySQL 5.6; see MySQL 3.23, 4.0, 4.1 Reference Manual for information regarding the old format.

  • TIME[(fsp)]

    A time. The range is '-838:59:59.000000' to '838:59:59.000000'. MySQL displays TIME values in 'HH:MM:SS[.fraction]' format, but permits assignment of values to TIME columns using either strings or numbers.

    As of MySQL 5.6.4, 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 0.

  • YEAR[(2|4)]

    A year in two-digit or four-digit format. The default is four-digit format. YEAR(2) or YEAR(4) differ in display format, but have the same range of values. In four-digit format, values display as 1901 to 2155, and 0000. In two-digit format, values display as 70 to 69, representing years from 1970 to 2069. MySQL displays YEAR values in YYYY or YYformat, but permits assignment of values to YEAR columns using either strings or numbers.

    For additional information about YEAR display format and inerpretation of input values, see Section 11.3.3, “The YEAR Type”.

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, 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 Section 5.1.6, “Server SQL Modes”.

Copyright © 1997, 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices

User Comments

Posted by Jeremy Wilson on October 24 2006 2:17pm[Delete] [Edit]

To select from a date range with MySql timestamp using the unix_timestamp, then display in human readable format. This is great with php, when you use drop down date ranges.
The $starttime and $endtime are varibals that I passed in my php script. I also made the varibles into a unix_timestamp in php using the mktime()

select date_format(FieldWithMysqlTimestamp1, '%b-%d-%Y') as Field1, Field2, Field3, date_format(FieldWithMysqlTimestamp2, '%b-%d-%Y') as Field4 from TableName where unix_timestamp(FieldWithMysqlTimestamp1) between $starttime and $endtime

Add your own comment.