The ability to record dates and times in a MySQL database is a very common requirement. This chapter presents the date and time functions for MySQL.
and time data comprises only numeric strings, so it can be stored in
a regular character column. However, by using temporal datatype
columns, you can use several built-in functions offered by MySQL.
Currently, five temporal datatypes are available:
date column type is
only for recording the date and uses the format
type is for recording time in the format
hhh:mm:ss. To record a combination of date and
time, you can use the
datetime column type:
yyyy-mm-dd hh:mm:ss. The
timestamp column is similar to
datetime, but is a little limited in its range of
allowable time: it starts at the Unix epoch time (i.e., 1970-01-01)
and ends at the end of 2037. Finally, the
datatype is used only for recording the year in a column.
Incidentally, any function that calls for a date or a time datatype will also accept a combined datetime datatype. For more information on date and time datatypes, see Appendix A.
Validation of date strings is limited: MySQL makes sure that months range only from 0 to 12, and days range from 0 to 31. Therefore, a date such as February 30 would be accepted. Version 5.0.2 of MySQL will offer more refined validation that would reject such a date.
At the end of this introduction is a listing of date and time functions, ...