Section 12.1. Date and Time Functions Grouped by Type

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 reject such a date.
However, some date functions accept 0 for some or all components of a date,
or incomplete date information (e.g., 2008-06-00). As a general rule, the
date and time functions that extract part of a date value usually accept
incomplete dates, but date and time functions that require complete date
information return NULL when given an incomplete date. The descriptions of
these functions in this chapter indicate which require valid dates and which
don't, as well as which return 0 or NULL for invalid dates.

The bulk of this chapter consists of an alphabetical listing of date
and time functions, with explanations of each. Each of the explanations
include an example of the function's use, along with a resulting display, if
any. For the examples in this chapter, I used the scenario of a professional
services firm (e.g., a law firm or an investment advisory firm) that tracks
appointments and seminars in MySQL. For help locating the appropriate
function, see the next section or the index at the end of this book.

12.1. Date and Time Functions Grouped by Type

Following are lists of date and time functions, grouped according to
their purpose: to retrieve the date or time, to extract an element from a
given date or time, or to perform calculations on given dates or
times.

12.1.1. Determining the Date or Time

[click here], [click here],
[click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here].

12.1.2. Extracting and Formatting the Date or Time

[click here], [click here],
[click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here].

12.1.3. Calculating and Modifying the Date or Time

[click here], [click here],
[click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here].