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:
Choosing a temporal data type
-
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.
Displaying dates and times
-
MySQL displays temporal values using specific formats by default, but you can produce other formats by using the appropriate functions.
Changing the client time zone
-
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.
Determining the current date or time
-
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.
Using 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.
Breaking dates or times into component values
-
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.
Synthesizing dates and times from component values
-
The complement of splitting apart temporal values is to create them from subparts.
Converting between dates or times and basic units
-
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.
Date and time arithmetic
-
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.
Selecting data based on temporal constraints
-
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.
