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 ...
Get MySQL in a Nutshell, 2nd Edition 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.