Chapter 6. Date and Time Functions
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.
Date
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, time,
datetime, timestamp, and
year. The date column type is
only for recording the date and uses the format
yyyy-mm-dd. The time column
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 year
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, ...
Get MySQL in a Nutshell now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
