Additional languages
MySQL 3.23, 4.0, 4.1 Reference Manual :: 11 Data Types :: 11.1 Data Type Overview :: 11.1.2 Overview of Date and Time Types
« 11.1.1 Overview of Numeric Types
11.1.3 Overview of String Types »
Section Navigation      [Toggle]

11.1.2. Overview of Date and Time Types

A summary of the temporal data types follows. For additional information, see Section 11.3, “Date and Time Types”. Storage requirements are given in Section 11.5, “Data Type Storage Requirements”.

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

The SUM() and AVG() aggregate functions do not work with temporal values. (They convert the values to numbers, which loses the part after the first non-numeric character.) To work around this problem, you can 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;

  • DATE

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

  • DATETIME

    A date and time combination. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. MySQL displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format, but allows you to assign values to DATETIME columns using either strings or numbers.

  • TIMESTAMP[(M)]

    A timestamp. The range is '1970-01-01 00:00:01' UTC to partway through the year 2038. 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.

    A TIMESTAMP column is useful for recording the date and time of an INSERT or UPDATE operation. By default, the first TIMESTAMP column in a table is automatically set to the date and time of the most recent operation if you do not assign it a value yourself. You can also set any TIMESTAMP column to the current date and time by assigning it a NULL value. Variations on automatic initialization and update properties are described in Section 11.3.1.2, “TIMESTAMP Properties as of MySQL 4.1”.

    In MySQL 4.1, TIMESTAMP is returned as a string with the format 'YYYY-MM-DD HH:MM:SS'. Display widths (used as described in the following paragraphs) are no longer supported; the display width is fixed at 19 characters. To obtain the value as a number, you should add +0 to the timestamp column.

    In MySQL 4.0 and earlier, TIMESTAMP values are displayed in YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, or YYMMDD format, depending on whether M is 14 (or missing), 12, 8, or 6, but allows you to assign values to TIMESTAMP columns using either strings or numbers. The M argument affects only how a TIMESTAMP column is displayed, not storage. Its values always are stored using four bytes each. From MySQL 4.0.12, the --new option can be used to make the server behave as in MySQL 4.1.

    Note that TIMESTAMP(M) columns where M is 8 or 14 are reported to be numbers, whereas other TIMESTAMP(M) columns are reported to be strings. This is just to ensure that you can reliably dump and restore the table with these types.

    Note: The behavior of TIMESTAMP columns changed considerably in MySQL 4.1. For complete information on the differences with regard to this data type in MySQL 4.1 and later versions (as opposed to MySQL 4.0 and earlier versions), be sure to see Section 11.3.1.1, “TIMESTAMP Properties Prior to MySQL 4.1”, and Section 11.3.1.2, “TIMESTAMP Properties as of MySQL 4.1”.

  • TIME

    A time. The range is '-838:59:59' to '838:59:59'. MySQL displays TIME values in 'HH:MM:SS' format, but allows you to assign values to TIME columns using either strings or numbers.

  • YEAR[(2|4)]

    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. MySQL displays YEAR values in YYYY format, but allows you to assign values to YEAR columns using either strings or numbers. The YEAR type is unavailable prior to MySQL 3.22.


User Comments

Posted by Jim Scarborough on August 30 2004 12:02pm[Delete] [Edit]

See also http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html for the functions like CURDATE() and others useful for initializing date and time type fields.

Posted by Philip Mak on January 18 2005 10:36am[Delete] [Edit]

You can also use "INT UNSIGNED" as a time data type, if you're using a programming language that represents time as the number of seconds since 1970 (e.g. Perl). The function UNIX_TIMESTAMP(NOW()) would return the current time.

Posted by Dan Von Kohorn on April 22 2005 7:44pm[Delete] [Edit]

To extract a timestamp in a human-readable format, use:

SELECT DATE_FORMAT(timestamp,'%l:%i:%s %p on %M %D, %Y') as ...
FROM ...

This returns:
9:15:37 PM on April 22nd, 2005

You can change the formatting or ordering in the single quotes as necessary. A complete list of the specifiers (like '%Y') are listed if you look up DATE_FORMAT.

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.