MySQL in a Nutshell, 2nd Edition

Chapter 12. Date and Time Functions

By using temporal data type columns, you can use several built-in functions offered by MySQL. This chapter presents those functions. Currently, five temporal data types are available: DATE, TIME, DATETIME, TIMESTAMP, and YEAR. You would set a column to one of these data types when creating or altering a table. See the descriptions of CREATE TABLE and ALTER TABLE in Chapter 6 for more details. The DATE column type can be used for recording just the date. It uses the yyyy-mm-dd format. The TIME column type is for recording time in the hhh:mm:ss format. To record a combination of date and time, use DATETIME: yyyy-mm-dd hh:mm:ss. The TIMESTAMP column is similar to DATETIME, but it is more limited in its range of allowable time: it starts at the Unix epoch time (i.e., 1970-01-01) and stops at the end of 2037. Plus, it has the distinction of resetting its value automatically when the row in which it is contained is updated, unless you specifically instruct MySQL otherwise. Finally, the YEAR data type is used only for recording the year in a column. For more information on date and time data types, see Appendix B.

Any function that calls for a date or a time data type will also accept a combined datetime data type. MySQL requires that months range from 0 to 12 and that days range from 0 to 31. Therefore, a date such as February 30 would be accepted prior to version 5.0.2 of MySQL. Beginning in version 5.0.2, MySQL offers more refined validation that would ...