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. It's important to understand them to avoid surprises in how MySQL interprets input data. MySQL also has reformatting functions for producing date and time output in formats other than the default. This chapter covers the following aspects of working with temporal values in MySQL:
MySQL displays temporal values using specific formats by default, but you can produce other formats by calling the appropriate function.
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.
This section explains how to split date and time values when you need only a piece, such as the month part or the hour part.
The complement of splitting apart temporal values is to create them from subparts. This section shows how.
Some date calculations 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. These conversions often are useful for interval calculations (such as time elapsed between two times).
It's possible in MySQL to add temporal intervals to date or time values to produce other dates or times, and to calculate the interval between dates or times. Time arithmetic is easier than date arithmetic. Times involve hours, minutes, and secondsunits that always have a fixed duration. Date arithmetic can be trickier because units such as months and years vary in length.
Using the techniques from the earlier sections, this one shows how to perform age calculation, relative date computation, date shifting, and leap year calculation.
The calculations discussed in the preceding sections to produce output values can also be used in WHERE clauses to specify how to select records using temporal conditions.
The TIMESTAMP column type has some special properties that make it convenient for automatically recording record creation and modification times. This section describes how TIMESTAMP columns behave and how to use them. It also discusses how to display TIMESTAMP values in more readable formats.
This chapter covers many of MySQL's functions for operating on date and time values, but there are yet 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, but 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.
5.1.1 MySQL's Date and Time Formats
MySQL provides DATE and TIME column types for representing date and time values separately, and a DATETIME type for combined date-and-time values. These values have the following formats:
Many of the examples in this chapter draw on the following tables, which contain columns representing TIME, DATE, DATETIME, and TIMESTAMP values. (The time_val table has two columns for use in time interval calculation examples.)
mysql> SELECT t1, t2 FROM time_val; +----------+----------+ | t1 | t2 | +----------+----------+ | 15:00:00 | 15:00:00 | | 05:01:30 | 02:30:20 | | 12:30:20 | 17:30:45 | +----------+----------+ mysql> SELECT d FROM date_val; +------------+ | d | +------------+ | 1864-02-28 | | 1900-01-15 | | 1987-03-05 | | 1999-12-31 | | 2000-06-04 | +------------+ mysql> SELECT dt FROM datetime_val; +---------------------+ | dt | +---------------------+ | 1970-01-01 00:00:00 | | 1987-03-05 12:30:15 | | 1999-12-31 09:00:00 | | 2000-06-04 15:45:30 | +---------------------+ mysql> SELECT ts FROM timestamp_val; +----------------+ | ts | +----------------+ | 19700101000000 | | 19870305123015 | | 19991231090000 | | 20000604154530 | +----------------+
Using the mysql Client Program
Writing MySQL-Based Programs
Record Selection Techniques
Working with Strings
Working with Dates and Times
Sorting Query Results
Generating Summaries
Modifying Tables with ALTER TABLE
Obtaining and Using Metadata
Importing and Exporting Data
Generating and Using Sequences
Using Multiple Tables
Statistical Techniques
Handling Duplicates
Performing Transactions
Introduction to MySQL on the Web
Incorporating Query Resultsinto Web Pages
Processing Web Input with MySQL
Using MySQL-Based Web Session Management
Appendix A. Obtaining MySQL Software
Appendix B. JSP and Tomcat Primer
Appendix C. References