Article: MySQL dates and times

A commented summary of date and time types in MySQL databases

category 'KB', language MySQL, created 15-Dec-2024, version V1.0, by Luc Pattyn

License: The author hereby grants you a worldwide, non-exclusive license to use and redistribute the files and the source code in the article in any way you see fit, provided you keep the copyright notice in place; when code modifications are applied, the notice must reflect that. The author retains copyright to the article, you may not republish or otherwise make available the article, in whole or in part, without the prior written consent of the author.

Disclaimer: This work is provided as is, without any express or implied warranties or conditions or guarantees. You, the user, assume all risk in its use. In no event will the author be liable to you on any legal theory for any special, incidental, consequential, punitive or exemplary damages arising out of this license or the use of the work or otherwise.

This short article summarizes some information on date and time data types and functions in MySQL.

DATE

The DATE type uses 4 bytes 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'.

YEAR

The YEAR type uses a single byte. MySQL retrieves and displays YEAR values in 'YYYY' format. It uses a single byte, the supported range is 1901 to 2155, and 0000.

The YEAR type must be avoided, it will go obsolete in the near future.

TIME

The TIME type uses 4 bytes for values with a time part but no date part. MySQL retrieves and displays TIME values in 'hh:mm:ss' format. TIME values may range from '00:00:00' to '23:59:59'.

Note: actually TIME can do more than that; for more details see here.

DATETIME and TIMESTAMP

The DATETIME field combines a DATE and a TIME, hence it uses 8 bytes.

The TIMESTAMP field also combines date and time information; it takes 4 bytes only by storing the 'UNIX timestamp' which equals the number of seconds since1970-01-01.

MySQL retrieves and displays DATETIME and TIMESTAMP values in 'YYYY-MM-DD HH:MM:SS' format.

The TIMESTAMP and DATETIME data types offer automatic initialization and updating to the current date and time.

The TIMESTAMP field uses the 'UNIX timestamp' which makes it compact but also limits its range to '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.499999'. This upper limit is bound to cause problems in the near future. Solutions could be found (e.g. a new type, or a global switch, that shifts the limits upwards), if not a migration towards the larger DATETIME type will become necessary.

Warning: there is a mayor difference between DATETIME and TIMESTAMP regarding time zone handling.

Functions

There are a lot of date/time related functions in MySQL, a list can be found here. Only a few of them are listed below:

NOW()	returns the current datetime or timestamp
DAY(), MONTH(), YEAR()	returns part of a date, datetime or timestamp, as a number, or NULL
HOUR(), MINUTE(), SECOND()	returns part of a time or datetime, as a number, or NULL
UNIX_TIMESTAMP()	returns the UNIX timestamp; it can be used to get at the integer value used to store a timestamp

Conclusion

MySQL offers a lot of functionality regarding dates and times. One of the most important things to remember is the relevant types can be operated upon using strings, there is no need to worry about the internal, numeric, encoding for dates and times.

Perceler

Last Modified 23-Dec-2024