MySQL has several data types for representing dates and times, and several functions for operating on them. MySQL stores dates and times in specific formats, and it’s important to understand them to avoid surprising results when you manipulate temporal data. This chapter covers the following aspects of working with date and time values in MySQL:
MySQL provides several temporal data types to choose from when you create tables. By knowing their properties, you’ll be able to choose them appropriately.
MySQL displays temporal values using specific formats by default, but you can produce other formats by using the appropriate functions.
The server interprets TIMESTAMP
values in the client’s current
time zone rather than its own. Clients in different time zones
should set their zone so that the server can properly interpret
TIMESTAMP
values for
them.
MySQL provides functions that return the date and time, which is useful for applications that need to know these values or need to calculate other temporal values in relation to them.
TIMESTAMP
values to track row
modifications
The TIMESTAMP
data
type has some special properties that make it convenient
for recording row creation and modification times
automatically.
You can split date and time values when you need only a piece, such as the month part of a date or the hour part of a time.
The complement of splitting apart temporal values is to create them from subparts.
Some temporal calculations such as date arithmetic operations are more easily performed using the number of days or seconds represented by a date or time value than by using the value itself. MySQL makes it possible to perform several kinds of conversions between date and time values and more basic units such as days or seconds.
You can add or subtract temporal values to produce other temporal values or calculate intervals between values. Applications for date and time arithmetic include age determination, relative date computation, and date shifting.
The calculations discussed in the preceding sections to
produce output values can also be used in WHERE
clauses to specify how to select
rows using temporal conditions.
This chapter covers several MySQL functions for operating on date and time values, but there are many others. To familiarize yourself with the full set, consult the MySQL Reference Manual. The variety of functions available to you means that it’s often possible to perform a given temporal calculation more than one way. I sometimes illustrate alternative methods for achieving a given result, and many of the problems addressed in this chapter can be solved in other ways than are shown here. I invite you to experiment to find other solutions. You may find a method that’s more efficient or that you find more readable.
Scripts that implement the recipes discussed in this chapter can
be found in the dates directory of
the recipes
source distribution. The
scripts that create the tables used here are located in the tables directory.
3.15.214.155