MySQL Cookbook (2007)

Chapter 6. Working with Dates and Times

Introduction

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.

Choosing a Temporal Data Type

Problem

You need to store temporal data but aren’t sure which is the most appropriate data type.

Solution

Choose the data type according to the characteristics of the information to be stored and how you need to use it. Consider questions such as these:

§ Do you need times only, dates only, or combined date and time values?

§ What range of values do you require?

§ Do you want automatic initialization of the column to the current date and time?

Discussion

MySQL provides DATE and TIME data types for representing date and time values separately, and DATETIME and TIMESTAMP types for combined date-and-time values. These values have the following characteristics:

§ DATE values are handled as strings inCCYY-MM-DD format, where CC, YY, MM, and DD represent the century, year within century, month, and day parts of the date. The supported range for DATE values is 1000-01-01 to 9999-12-31.

§ TIME values are represented as strings inhh:mm:ss format, where hh, mm, and ss are the hours, minutes, and seconds parts of the time. TIME values often can be thought of as time-of-day values, but MySQL actually treats them as elapsed time. Thus, they may be greater than 23:59:59or even negative. (The actual range of a TIME column is -838:59:59 to 838:59:59.)

§ DATETIME and TIMESTAMP values are represented as combined date-and-time strings in CCYY-MM-DDhh:mm:ss format. (Before MySQL 4.1, TIMESTAMP display format was CCYYMMDDhhmmss numeric format. Older applications that depend on this display format must be updated for MySQL 4.1 and up.)

In many respects, you can treat the DATETIME and TIMESTAMP data types similarly, but watch out for these differences:

§ DATETIME has a supported range of 1000-01-01 00:00:00 to 9999-12-31 23:59:59, whereas TIMESTAMP values are valid only from the year 1970 to approximately 2037.

§ The TIMESTAMP type has special auto-initialization and auto-update properties that are discussed further in Using TIMESTAMP to Track Row Modification Times.

§ When a client inserts a TIMESTAMP value, the server converts it from the time zone associated with the client connection to UTC and stores the UTC value. When the client retrieves a TIMESTAMP value, the server performs the reverse operation to convert the UTC value back to the client connection time zone. Clients that are in different time zones from the server can configure their connection so that this conversion is appropriate for their own time zone (Setting the Client Time Zone).

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 |

+---------------------+

| 1970-01-01 00:00:00 |

| 1987-03-05 12:30:15 |

| 1999-12-31 09:00:00 |

| 2000-06-04 15:45:30 |

+---------------------+

It is a good idea to create the time_val, date_val, datetime_val, and timestamp_val tables right now before reading further. (Use the appropriate scripts in the tables directory of the recipes distribution.)

Changing MySQL’s Date Format

Problem

You want to change the ISO format that MySQL uses for representing date values.

Solution

You can’t. However, you can rewrite non-ISO input values into ISO format when storing dates, and you can rewrite ISO values to other formats for display by using the DATE_FORMAT() function.

Discussion

The CCYY-MM-DD format that MySQL uses for DATE values follows the ISO 8601 standard for representing dates. Because the year, month, and day parts have a fixed length and appear left to right in date strings, this format has the useful property that dates sort naturally into the proper temporal order. Chapters 7 and 8 discuss ordering and grouping techniques for date-based values.

ISO format, although common, is not used by all database systems, which can cause problems if you want to move data between different systems. Moreover, people commonly like to represent dates in other formats such as MM/DD/YY or DD-MM-CCYY. This too can be a source of trouble, due to mismatches between human expectations of what dates should look like and the way that MySQL actually represents them.

A question frequently asked by people who are new to MySQL is, “How do I tell MySQL to store dates in a specific format such as MM/DD/CCYY?” That’s the wrong question. The right question is, “If I have a date in a specific format, how can I store it in MySQL’s supported format, and vice versa?” MySQL always stores dates in ISO format, a fact that has implications both for data entry and for processing retrieved query results:

§ For data-entry purposes, to store values that are not in ISO format, you normally must rewrite them first. If you don’t want to rewrite your dates, you need to store them as strings (for example, in a CHAR column). But then you can’t operate on them as dates.

Chapter 10 covers the topic of date rewriting for data entry. That chapter also discusses checking dates to verify that they’re valid. In some cases, if your values are close to ISO format, rewriting may not be necessary. For example, MySQL interprets the string values 87-1-7 and 1987-1-7 and the numbers 870107 and 19870107 as the date 1987-01-07 when they are loaded into a DATE column.

§ For display purposes, you can rewrite dates to non-ISO formats by using the DATE_FORMAT() function. It provides a lot of flexibility for changing date values into other formats (see later in this section). You can also use functions such as YEAR() to extract parts of dates for display (Extracting Parts of Dates or Times). Additional discussion can be found in Chapter 10.

When you enter date values, one way to rewrite non-ISO dates is to use the STR_TO_DATE() function. STR_TO_DATE() takes a string representing a temporal value and a format string that specifies the “syntax” of the value. Within the formatting string, you use special sequences of the form %c, where c specifies which part of the date to expect. For example, %Y, %M, and %d signify the four-digit year, the month name, and the two-digit day of the month. To insert the value May 13, 2007 into a DATE column, you can do this:

mysql>INSERT INTO t (d) VALUES(STR_TO_DATE('May 13, 2007','%M %d, %Y'));

mysql> SELECT d FROM t;

+------------+

| d |

+------------+

| 2007-05-13 |

+------------+

For date display, MySQL uses ISO format (CCYY-MM-DD) unless you tell it otherwise. If you want to display dates or times in a format other than what MySQL uses by default, use the DATE_FORMAT() or TIME_FORMAT() functions to rewrite them. If you require a more specialized format that those functions cannot provide, write a stored function.

To rewrite date values into other formats, use the DATE_FORMAT() function, which takes two arguments: A DATE, DATETIME, or TIMESTAMP value, and a string describing how to display the value. The format string uses the same kind of specifiers as STR_TO_DATE(). The following statement shows the values in the date_val table, both as MySQL displays them by default and as reformatted with DATE_FORMAT():

mysql>SELECT d, DATE_FORMAT(d,'%M %d, %Y') FROM date_val;

+------------+----------------------------+

| d | DATE_FORMAT(d,'%M %d, %Y') |

+------------+----------------------------+

| 1864-02-28 | February 28, 1864 |

| 1900-01-15 | January 15, 1900 |

| 1987-03-05 | March 05, 1987 |

| 1999-12-31 | December 31, 1999 |

| 2000-06-04 | June 04, 2000 |

+------------+----------------------------+

DATE_FORMAT() tends to produce rather long column headings, so it’s often useful to provide an alias to make a heading more concise or meaningful:

mysql>SELECT d, DATE_FORMAT(d,'%M %d, %Y') AS date FROM date_val;

+------------+-------------------+

| d | date |

+------------+-------------------+

| 1864-02-28 | February 28, 1864 |

| 1900-01-15 | January 15, 1900 |

| 1987-03-05 | March 05, 1987 |

| 1999-12-31 | December 31, 1999 |

| 2000-06-04 | June 04, 2000 |

+------------+-------------------+

The MySQL Reference Manual provides a complete list of format sequences to use with DATE_FORMAT(), TIME_FORMAT(), and STR_TO_DATE(). Some of the more commonly used ones are shown in the following table:

The time-related format sequences shown in the table are useful only when you pass DATE_FORMAT() a value that has both date and time parts (a DATETIME or TIMESTAMP). The following statement demonstrates how to display DATETIME values from the datetime_val table using formats that include the time of day:

mysql>SELECT dt,

-> DATE_FORMAT(dt,'%c/%e/%y %r') AS format1,

-> DATE_FORMAT(dt,'%M %e, %Y %T') AS format2

-> FROM datetime_val;

+---------------------+----------------------+----------------------------+

| dt | format1 | format2 |

+---------------------+----------------------+----------------------------+

| 1970-01-01 00:00:00 | 1/1/70 12:00:00 AM | January 1, 1970 00:00:00 |

| 1987-03-05 12:30:15 | 3/5/87 12:30:15 PM | March 5, 1987 12:30:15 |

| 1999-12-31 09:00:00 | 12/31/99 09:00:00 AM | December 31, 1999 09:00:00 |

| 2000-06-04 15:45:30 | 6/4/00 03:45:30 PM | June 4, 2000 15:45:30 |

+---------------------+----------------------+----------------------------+

TIME_FORMAT() is similar to DATE_FORMAT(), but it understands only time-related specifiers in the format string. TIME_FORMAT() works with TIME, DATETIME, or TIMESTAMP values.

mysql>SELECT dt,

-> TIME_FORMAT(dt, '%r') AS '12-hour time',

-> TIME_FORMAT(dt, '%T') AS '24-hour time'

-> FROM datetime_val;

+---------------------+--------------+--------------+

| dt | 12-hour time | 24-hour time |

+---------------------+--------------+--------------+

| 1970-01-01 00:00:00 | 12:00:00 AM | 00:00:00 |

| 1987-03-05 12:30:15 | 12:30:15 PM | 12:30:15 |

| 1999-12-31 09:00:00 | 09:00:00 AM | 09:00:00 |

| 2000-06-04 15:45:30 | 03:45:30 PM | 15:45:30 |

+---------------------+--------------+--------------+

If DATE_FORMAT() or TIME_FORMAT() cannot produce the results that you want, perhaps you can write a stored function that does. Suppose that you want to convert 24-hour TIME values to 12-hour format but with a suffix of a.m. or p.m. rather than AM or PM. The following function accomplishes that task. It uses TIME_FORMAT() to do most of the work, and then strips off the suffix supplied by %r and replaces it with the desired suffix:

CREATE FUNCTION time_ampm (t TIME)

RETURNS VARCHAR(13) # mm:dd:ss {a.m.|p.m.} format

BEGIN

DECLARE ampm CHAR(4);

IF TIME_TO_SEC(t) < 12*60*60 THEN

SET ampm = 'a.m.';

ELSE

SET ampm = 'p.m.';

END IF;

RETURN CONCAT(LEFT(TIME_FORMAT(t, '%r'),9),ampm);

END;

Use the function like this:

mysql>SELECT t1, time_ampm(t1) FROM time_val;

+----------+---------------+

| t1 | time_ampm(t1) |

+----------+---------------+

| 15:00:00 | 03:00:00 p.m. |

| 05:01:30 | 05:01:30 a.m. |

| 12:30:20 | 12:30:20 p.m. |

+----------+---------------+

For more information about writing stored functions, see Chapter 16.

Setting the Client Time Zone

Problem

You have a client that is in a different time zone from the server, so when it stores TIMESTAMP values, they don’t have the correct UTC values.

Solution

Have the client specify its time zone when it connects to the server by setting the time_zone system variable.

Discussion

MySQL interprets TIMESTAMP values with respect to each client’s time zone. When a client inserts a TIMESTAMP value, the server converts it from the time zone associated with the client connection to UTC and stores the UTC value. (Internally, the server stores a TIMESTAMP value as the number of seconds since 1970-01-01 00:00:00 UTC.) When the client retrieves a TIMESTAMP value, the server performs the reverse operation to convert the UTC value back to the client connection time zone.

The default connection time zone is the server’s time zone. The server examines its operating environment when it starts to determine this setting. (To use a different value, start the server with the --default-time-zone option.) If all clients are in the same time zone as the server, nothing special need be done for the proper TIMESTAMP time zone conversion to occur. But if a client is running in a time zone different from the server and inserts TIMESTAMP values, the UTC values won’t be correct.

Suppose that the server and client A are in the same time zone, and client A issues these statements:

mysql>CREATE TABLE t (ts TIMESTAMP);

mysql> INSERT INTO t (ts) VALUES('2006-06-01 12:30:00');

mysql> SELECT ts FROM t;

+---------------------+

| ts |

+---------------------+

| 2006-06-01 12:30:00 |

+---------------------+

Here, client A sees the same value that it stored. A different client B will also see the same value if it retrieves it, but if client B is in a different time zone, that value isn’t correct for its zone. Conversely, if client B stores a value, that value when returned by client A won’t be correct for the client A time zone.

To deal with this problem so that TIMESTAMP conversions happen for the correct time zone, a client can set its time zone explicitly. To specify the client time zone, set the session value of the time_zone system variable. Suppose that the server has a global time zone of six hours ahead of UTC. Each client is assigned that same value as its initial session time zone:

mysql>SELECT @@global.time_zone, @@session.time_zone;

+--------------------+---------------------+

| @@global.time_zone | @@session.time_zone |

+--------------------+---------------------+

| +06:00 | +06:00 |

+--------------------+---------------------+

Client B mentioned earlier will see the same TIMESTAMP value as client A when it connects:

mysql>SELECT ts FROM t;

+---------------------+

| ts |

+---------------------+

| 2006-06-01 12:30:00 |

+---------------------+

If client B is only four hours ahead of UTC, it can set its time zone after connecting like this:

mysql>SET SESSION time_zone = '+04:00';

mysql> SELECT @@global.time_zone, @@session.time_zone;

+--------------------+---------------------+

| @@global.time_zone | @@session.time_zone |

+--------------------+---------------------+

| +06:00 | +04:00 |

+--------------------+---------------------+

Then when client B retrieves the TIMESTAMP value, it will be properly adjusted for its own time zone:

mysql>SELECT ts FROM t;

+---------------------+

| ts |

+---------------------+

| 2006-06-01 10:30:00 |

+---------------------+

The client time zone also affects the values displayed from functions that return the current date and time (Determining the Current Date or Time).

See Also

To convert individual date-and-time values from one time zone to another, use the CONVERT_TZ() function (Shifting a Date-and-Time Value to a Different Time Zone).

Determining the Current Date or Time

Problem

What’s today’s date? What time is it?

Solution

Use the CURDATE() , CURTIME() or NOW() functions to get values expressed with respect to the connection time zone. Use UTC_DATE(), UTC_TIME(), or UTC_TIMESTAMP() for values in UTC time.

Discussion

Some applications need to know the current date or time, such as those that write log records tagged with the current date and time. This kind of information is also useful for date calculations that are performed in relation to the current date, such as finding the first (or last) day of the month, or determining the date for Wednesday of next week.

The current date and time are available through three functions. CURDATE() and CURTIME() return the date and time separately, and NOW() returns them both as a date-and-time value:

mysql>SELECT CURDATE(), CURTIME(), NOW();

+------------+-----------+---------------------+

| CURDATE() | CURTIME() | NOW() |

+------------+-----------+---------------------+

| 2006-06-03 | 09:41:50 | 2006-06-03 09:41:50 |

+------------+-----------+---------------------+

CURRENT_DATE and CURRENT_TIME are synonyms for CURDATE() and CURTIME(). CURRENT_TIMESTAMP is a synonym for NOW().

The preceding functions return values for the client’s connection time zone (Setting the Client Time Zone). For values in UTC time, use the UTC_DATE(), UTC_TIME(), or UTC_TIMESTAMP() functions instead.

To obtain subparts of these values (such as the current day of the month or current hour of the day), use the techniques discussed in Extracting Parts of Dates or Times.

Using TIMESTAMP to Track Row Modification Times

Problem

You want a row’s creation time or last modification time to be recorded automatically.

Solution

Use the TIMESTAMP data type, which has auto-initialization and auto-update properties.

Discussion

MySQL supports a TIMESTAMP data type that stores date-and-time values. Earlier sections covered the range of values for TIMESTAMP (Choosing a Temporal Data Type) and the conversion of TIMESTAMP values to and from UTC when they are stored and retrieved (Setting the Client Time Zone). This section focuses on how TIMESTAMP columns enable you to track row creation and update times automatically:

§ One TIMESTAMP column in a table can be treated as special in either or both of the following ways:

§ The column is automatically initialized to the current date and time when new rows are created. This means you need not specify its value at all in an INSERT statement; MySQL initializes it automatically to the row’s creation time. (This also occurs if you set the column to NULL.)

§ The column is automatically updated to the current date and time when you change any other column in the row from its current value. The update happens only if you actually change a column value; setting a column to its current value doesn’t update the TIMESTAMP.

This auto-update property sometimes surprises people who don’t realize that changing another column also updates the TIMESTAMP column. This will never surprise you, of course, because you’re aware of it!

§ There can be multiple TIMESTAMP columns in a table, but only one of them can have the special properties just described. Other TIMESTAMP columns have a default value of zero, not the current date and time. Also, their value does not change automatically when you modify other columns; to update them, you must change them yourself.

§ A TIMESTAMP column can be updated to the current date and time at any time by setting it to NULL, unless it has specifically been defined to allow NULL values. This is true for any TIMESTAMP column, not just the first one.

The special properties that relate to row creation and modification make the TIMESTAMP data type particularly suited for certain kinds of problems, such as automatically recording the times at which table rows are inserted or updated. The following discussion shows how to take advantage of these properties.

The syntax for defining TIMESTAMP columns is covered in full in the MySQL Reference Manual. Here we cover only some simple cases. By default, if you specify no special handling for the first TIMESTAMP column in a table, it is equivalent to specifying that it should have both the auto-initialize and auto-update properties explicitly. You can see this as follows, where the SHOW CREATE TABLE statement displays the full TIMESTAMP definition that results from the CREATE TABLE statement:

mysql>CREATE TABLE t (ts TIMESTAMP);

mysql> SHOW CREATE TABLE t\G

*************************** 1. row ***************************

Table: t

Create Table: CREATE TABLE `t` (

`ts` timestamp NOT NULL

DEFAULT CURRENT_TIMESTAMP

ON UPDATE CURRENT_TIMESTAMP

) ENGINE=MyISAM DEFAULT CHARSET=latin1

If you explicitly specify only the DEFAULT or ON UPDATE attribute for the TIMESTAMP column, it has only that attribute and not the other one.

The NOT NULL shown in the column definition might seem a curious thing, given that you can insert NULL into the column, and no error occurs. What this means is that, although you can specify NULL as the value to be inserted, you cannot store NULL because MySQL stores the current date and time instead.

To create a table in which each row contains a value that indicates when the row was created or most recently updated, include a TIMESTAMP column. MySQL will set the column to the current date and time when you create a new row, and update the column whenever you update the value of another column in the row. Suppose that you create a table tsdemo1 with a TIMESTAMP as one of its columns:

CREATE TABLE tsdemo1

(

ts TIMESTAMP,

val INT

);

In this case, the ts column has both the auto-initialize and auto-update properties, for reasons just discussed. Insert a couple of rows into the table, and then select its contents. (Issue the INSERT statements a few seconds apart so that the timestamps differ.) The first INSERT statement shows that you can set ts to the current date and time by omitting it from the INSERT statement entirely; the second shows that you can do so by setting ts explicitly to NULL:

mysql>INSERT INTO tsdemo1 (val) VALUES(5);

mysql> INSERT INTO tsdemo1 (ts,val) VALUES(NULL,10);

mysql> SELECT * FROM tsdemo1;

+---------------------+------+

| ts | val |

+---------------------+------+

| 2006-06-03 08:21:26 | 5 |

| 2006-06-03 08:21:31 | 10 |

+---------------------+------+

Now issue a statement that changes one row’s val column, and check its effect on the table’s contents:

mysql>UPDATE tsdemo1 SET val = 6 WHERE val = 5;

mysql> SELECT * FROM tsdemo1;

+---------------------+------+

| ts | val |

+---------------------+------+

| 2006-06-03 08:21:52 | 6 |

| 2006-06-03 08:21:31 | 10 |

+---------------------+------+

The result shows that the TIMESTAMP column of the modified row was updated.

If you modify multiple rows, the TIMESTAMP value in each of them is updated:

mysql>UPDATE tsdemo1 SET val = val + 1;

mysql> SELECT * FROM tsdemo1;

+---------------------+------+

| ts | val |

+---------------------+------+

| 2006-06-03 08:22:00 | 7 |

| 2006-06-03 08:22:00 | 11 |

+---------------------+------+

An UPDATE statement that doesn’t actually change the value in the val column doesn’t update the TIMESTAMP value. To see this, set every row’s val column to its current value, and then review the contents of the table:

mysql>UPDATE tsdemo1 SET val = val;

mysql> SELECT * FROM tsdemo1;

+---------------------+------+

| ts | val |

+---------------------+------+

| 2006-06-03 08:22:00 | 7 |

| 2006-06-03 08:22:00 | 11 |

+---------------------+------+

If you want the TIMESTAMP column to be set initially to the time at which a row is created, but to remain constant thereafter, define it to auto-initialize but not auto-update. To do this, the TIMESTAMP definition can be given as shown in the following table:

CREATE TABLE tsdemo2

(

t_create TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

val INT

);

Create the table, and then insert records into it with the TIMESTAMP column not specified (or specified as NULL) to initialize it to the current date and time:

mysql>INSERT INTO tsdemo2 (val) VALUES(5);

mysql> INSERT INTO tsdemo2 (t_create,val) VALUES(NULL,10);

mysql> SELECT * FROM tsdemo2;

+---------------------+------+

| t_create | val |

+---------------------+------+

| 2006-06-03 08:26:00 | 5 |

| 2006-06-03 08:26:05 | 10 |

+---------------------+------+

After inserting the records, change the val column, and then verify that the update leaves the t_create column unchanged (that is, set to the record-creation time):

mysql>UPDATE tsdemo2 SET val = val + 1;

mysql> SELECT * FROM tsdemo2;

+---------------------+------+

| t_create | val |

+---------------------+------+

| 2006-06-03 08:26:00 | 6 |

| 2006-06-03 08:26:05 | 11 |

+---------------------+------+

See Also

If you want to simulate the TIMESTAMP auto-initialization and auto-update properties for other temporal types, you can use triggers (Simulating TIMESTAMP Properties for Other Date and Time Types ).

Extracting Parts of Dates or Times

Problem

You want to obtain just a part of a date or a time.

Solution

You have several options:

§ Invoke a function specifically intended for extracting part of a temporal value, such as MONTH() or MINUTE(). This is usually the fastest method for component extraction if you need only a single component of a value.

§ Use a formatting function such as DATE_FORMAT() or TIME_FORMAT() with a format string that includes a specifier for the part of the value you want to obtain.

§ Treat a temporal value as a string and use a function such as LEFT() or MID() to extract substrings corresponding to the desired part of the value.

Discussion

The following discussion shows different ways to extract parts of temporal values.

Decomposing dates or times using component-extraction functions

MySQL includes many functions for extracting date or time parts from temporal values. For example, DATE() or TIME() extracts the date or time part of temporal values:

mysql>SELECT dt, DATE(dt), TIME(dt) FROM datetime_val;

+---------------------+------------+----------+

| dt | DATE(dt) | TIME(dt) |

+---------------------+------------+----------+

| 1970-01-01 00:00:00 | 1970-01-01 | 00:00:00 |

| 1987-03-05 12:30:15 | 1987-03-05 | 12:30:15 |

| 1999-12-31 09:00:00 | 1999-12-31 | 09:00:00 |

| 2000-06-04 15:45:30 | 2000-06-04 | 15:45:30 |

+---------------------+------------+----------+

Some of the other component-extraction functions are shown in the following list; consult the MySQL Reference Manual for a complete list. The date-related functions work with DATE, DATETIME, or TIMESTAMP values. The time-related functions work with TIME, DATETIME, orTIMESTAMP values.

Here’s an example:

mysql>SELECT dt,

-> YEAR(dt), DAYOFMONTH(dt),

-> HOUR(dt), SECOND(dt)

-> FROM datetime_val;

+---------------------+----------+----------------+----------+------------+

| dt | YEAR(dt) | DAYOFMONTH(dt) | HOUR(dt) | SECOND(dt) |

+---------------------+----------+----------------+----------+------------+

| 1970-01-01 00:00:00 | 1970 | 1 | 0 | 0 |

| 1987-03-05 12:30:15 | 1987 | 5 | 12 | 15 |

| 1999-12-31 09:00:00 | 1999 | 31 | 9 | 0 |

| 2000-06-04 15:45:30 | 2000 | 4 | 15 | 30 |

+---------------------+----------+----------------+----------+------------+

Functions such as YEAR() or DAYOFMONTH() extract values that have an obvious correspondence to a substring of the temporal value to which you apply them. Other date component-extraction functions provide access to values that have no such correspondence. One is the day-of-year value:

mysql>SELECT d, DAYOFYEAR(d) FROM date_val;

+------------+--------------+

| d | DAYOFYEAR(d) |

+------------+--------------+

| 1864-02-28 | 59 |

| 1900-01-15 | 15 |

| 1987-03-05 | 64 |

| 1999-12-31 | 365 |

| 2000-06-04 | 156 |

+------------+--------------+

Another is the day of the week, which can be obtained either by name or by number:

§ DAYNAME() returns the complete day name. There is no function for returning the three-character name abbreviation, but you can get it easily by passing the full name to LEFT():

§ mysql>SELECT d, DAYNAME(d), LEFT(DAYNAME(d),3) FROM date_val;

§ +------------+------------+--------------------+

§ | d | DAYNAME(d) | LEFT(DAYNAME(d),3) |

§ +------------+------------+--------------------+

§ | 1864-02-28 | Sunday | Sun |

§ | 1900-01-15 | Monday | Mon |

§ | 1987-03-05 | Thursday | Thu |

§ | 1999-12-31 | Friday | Fri |

§ | 2000-06-04 | Sunday | Sun |

+------------+------------+--------------------+

§ To get the day of the week as a number, use DAYOFWEEK() or WEEKDAY(), but pay attention to the range of values each function returns. DAYOFWEEK() returns values from 1 to 7, corresponding to Sunday through Saturday: WEEKDAY() returns values from 0 to 6, corresponding to Monday through Sunday.

§ mysql>SELECT d, DAYNAME(d), DAYOFWEEK(d), WEEKDAY(d) FROM date_val;

§ +------------+------------+--------------+------------+

§ | d | DAYNAME(d) | DAYOFWEEK(d) | WEEKDAY(d) |

§ +------------+------------+--------------+------------+

§ | 1864-02-28 | Sunday | 1 | 6 |

§ | 1900-01-15 | Monday | 2 | 0 |

§ | 1987-03-05 | Thursday | 5 | 3 |

§ | 1999-12-31 | Friday | 6 | 4 |

§ | 2000-06-04 | Sunday | 1 | 6 |

+------------+------------+--------------+------------+

EXTRACT() is another function for obtaining individual parts of temporal values:

mysql>SELECT dt,

-> EXTRACT(DAY FROM dt),

-> EXTRACT(HOUR FROM dt)

-> FROM datetime_val;

+---------------------+----------------------+-----------------------+

| dt | EXTRACT(DAY FROM dt) | EXTRACT(HOUR FROM dt) |

+---------------------+----------------------+-----------------------+

| 1970-01-01 00:00:00 | 1 | 0 |

| 1987-03-05 12:30:15 | 5 | 12 |

| 1999-12-31 09:00:00 | 31 | 9 |

| 2000-06-04 15:45:30 | 4 | 15 |

+---------------------+----------------------+-----------------------+

The keyword indicating what to extract from the value should be a unit specifier such as YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND. (Check the MySQL Reference Manual for the full list.) Note that each unit specifier is given in singular form, not plural.

OBTAINING THE CURRENT YEAR, MONTH, DAY, HOUR, MINUTE, OR SECOND

The extraction functions shown in this recipe can be applied to CURDATE() or NOW() to obtain the current year, month, day, or day of week:

mysql>SELECT CURDATE(), YEAR(CURDATE()) AS year,

-> MONTH(CURDATE()) AS month, MONTHNAME(CURDATE()) AS monthname,

-> DAYOFMONTH(CURDATE()) AS day, DAYNAME(CURDATE()) AS dayname;

+------------+------+-------+-----------+------+----------+

| CURDATE() | year | month | monthname | day | dayname |

+------------+------+-------+-----------+------+----------+

| 2006-06-03 | 2006 | 6 | June | 3 | Saturday |

+------------+------+-------+-----------+------+----------+

Similarly, to obtain the current hour, minute, or second, pass CURTIME() or NOW() to a time-component function:

mysql>SELECT NOW(), HOUR(NOW()) AS hour,

-> MINUTE(NOW()) AS minute, SECOND(NOW()) AS second;

+---------------------+------+--------+--------+

| NOW() | hour | minute | second |

+---------------------+------+--------+--------+

| 2006-06-03 09:45:32 | 9 | 45 | 32 |

+---------------------+------+--------+--------+

Decomposing dates or times using formatting functions

The DATE_FORMAT() and TIME_FORMAT() functions reformat date and time values. By specifying appropriate format strings, you can extract individual parts of temporal values:

mysql>SELECT dt,

-> DATE_FORMAT(dt,'%Y') AS year,

-> DATE_FORMAT(dt,'%d') AS day,

-> TIME_FORMAT(dt,'%H') AS hour,

-> TIME_FORMAT(dt,'%s') AS second

-> FROM datetime_val;

+---------------------+------+------+------+--------+

| dt | year | day | hour | second |

+---------------------+------+------+------+--------+

| 1970-01-01 00:00:00 | 1970 | 01 | 00 | 00 |

| 1987-03-05 12:30:15 | 1987 | 05 | 12 | 15 |

| 1999-12-31 09:00:00 | 1999 | 31 | 09 | 00 |

| 2000-06-04 15:45:30 | 2000 | 04 | 15 | 30 |

+---------------------+------+------+------+--------+

Formatting functions enable you to extract more than one part of a value. For example, to extract the entire date or time from DATETIME values, do this:

mysql>SELECT dt,

-> DATE_FORMAT(dt,'%Y-%m-%d') AS 'date part',

-> TIME_FORMAT(dt,'%T') AS 'time part'

-> FROM datetime_val;

+---------------------+------------+-----------+

| dt | date part | time part |

+---------------------+------------+-----------+

| 1970-01-01 00:00:00 | 1970-01-01 | 00:00:00 |

| 1987-03-05 12:30:15 | 1987-03-05 | 12:30:15 |

| 1999-12-31 09:00:00 | 1999-12-31 | 09:00:00 |

| 2000-06-04 15:45:30 | 2000-06-04 | 15:45:30 |

+---------------------+------------+-----------+

One advantage of using formatting functions is that you can display the extracted values in a different form from that in which they’re present in the original values. If you want to present a date differently from CCYY-MM-DD format or present a time without the seconds part, that’s easy to do:

mysql>SELECT ts,

-> DATE_FORMAT(ts,'%M %e, %Y') AS 'descriptive date',

-> TIME_FORMAT(ts,'%H:%i') AS 'hours/minutes'

-> FROM timestamp_val;

+---------------------+-------------------+---------------+

| ts | descriptive date | hours/minutes |

+---------------------+-------------------+---------------+

| 1970-01-01 00:00:00 | January 1, 1970 | 00:00 |

| 1987-03-05 12:30:15 | March 5, 1987 | 12:30 |

| 1999-12-31 09:00:00 | December 31, 1999 | 09:00 |

| 2000-06-04 15:45:30 | June 4, 2000 | 15:45 |

+---------------------+-------------------+---------------+

Decomposing dates or times using string functions

The discussion in this section thus far has shown how to extract components of temporal values using functions such as YEAR(), MONTH(), and DATE_FORMAT(). If you pass a date or time value to a string function, MySQL treats it as a string, so string functions provide another way to decompose temporal values. This means that you can extract pieces of temporal values by using functions such as LEFT() or MID() to pull out substrings:

mysql>SELECT dt,

-> LEFT(dt,4) AS year,

-> MID(dt,9,2) AS day,

-> RIGHT(dt,2) AS second

-> FROM datetime_val;

+---------------------+------+------+--------+

| dt | year | day | second |

+---------------------+------+------+--------+

| 1970-01-01 00:00:00 | 1970 | 01 | 00 |

| 1987-03-05 12:30:15 | 1987 | 05 | 15 |

| 1999-12-31 09:00:00 | 1999 | 31 | 00 |

| 2000-06-04 15:45:30 | 2000 | 04 | 30 |

+---------------------+------+------+--------+

You can obtain the entire date or time part from DATETIME or TIMESTAMP values using string-extraction functions such as LEFT() or RIGHT():

mysql>SELECT dt,

-> LEFT(dt,10) AS date,

-> RIGHT(dt,8) AS time

-> FROM datetime_val;

+---------------------+------------+----------+

| dt | date | time |

+---------------------+------------+----------+

| 1970-01-01 00:00:00 | 1970-01-01 | 00:00:00 |

| 1987-03-05 12:30:15 | 1987-03-05 | 12:30:15 |

| 1999-12-31 09:00:00 | 1999-12-31 | 09:00:00 |

| 2000-06-04 15:45:30 | 2000-06-04 | 15:45:30 |

+---------------------+------------+----------+

mysql> SELECT ts,

-> LEFT(ts,10) AS date,

-> RIGHT(ts,8) AS time

-> FROM timestamp_val;

+---------------------+------------+----------+

| ts | date | time |

+---------------------+------------+----------+

| 1970-01-01 00:00:00 | 1970-01-01 | 00:00:00 |

| 1987-03-05 12:30:15 | 1987-03-05 | 12:30:15 |

| 1999-12-31 09:00:00 | 1999-12-31 | 09:00:00 |

| 2000-06-04 15:45:30 | 2000-06-04 | 15:45:30 |

+---------------------+------------+----------+

Decomposition of temporal values with string functions is subject to a couple of constraints that component extraction and reformatting functions are not bound by:

§ To use a substring function such as LEFT(), MID(), or RIGHT(), you must have fixed-length strings. Although MySQL interprets the value 1987-1-1 as 1987-01-01 if you insert it into a DATE column, using RIGHT('1987-1-1',2) to extract the day part will not work. For values that have variable-length substrings, you may be able to use SUBSTRING_INDEX() instead. Alternatively, if your values are close to ISO format, perhaps you can standardize them to ISO format using the techniques described in Canonizing Not-Quite-ISO Date Strings. This converts them to have fixed-length substrings before you attempt to extract subparts.

§ String functions cannot be used to obtain values that don’t correspond to substrings of a date value, such as the day of the week or the day of the year.

Synthesizing Dates or Times from Component Values

Problem

You want to produce a new date from a given date by replacing parts of its values. Or you have the parts of a date or time and want to combine them to produce a date or time value.

Solution

You have several options:

§ Use MAKETIME() to construct a TIME value from hour, minute, and second parts.

§ Use DATE_FORMAT() or TIME_FORMAT() to combine parts of the existing value with parts you want to replace.

§ Pull out the parts that you need with component-extraction functions and recombine the parts with CONCAT().

Discussion

The reverse of splitting a date or time value into components is synthesizing a temporal value from its constituent parts. Techniques for date and time synthesis include using composition functions, formatting functions, and string concatenation.

The MAKETIME() function takes component hour, minute, and second values as arguments and combines them to produce a time:

mysql>SELECT MAKETIME(10,30,58), MAKETIME(-5,0,11);

+--------------------+-------------------+

| MAKETIME(10,30,58) | MAKETIME(-5,0,11) |

+--------------------+-------------------+

| 10:30:58 | -05:00:11 |

+--------------------+-------------------+

There is also a MAKEDATE() function, but its arguments are year and day-of-year values:

mysql>SELECT MAKEDATE(2007,60);

+-------------------+

| MAKEDATE(2007,60) |

+-------------------+

| 2007-03-01 |

+-------------------+

I don’t find MAKEDATE() very useful because I’m much more likely to be working with year, month, and day values than year and day-of-year values.

Date synthesis often is performed by beginning with a given date, and then keeping parts that you want to use and replacing the rest. For example, to produce the first day of the month in which a date falls, use DATE_FORMAT() to extract the year and month parts from the date and combine them with a day value of 01:

mysql>SELECT d, DATE_FORMAT(d,'%Y-%m-01') FROM date_val;

+------------+---------------------------+

| d | DATE_FORMAT(d,'%Y-%m-01') |

+------------+---------------------------+

| 1864-02-28 | 1864-02-01 |

| 1900-01-15 | 1900-01-01 |

| 1987-03-05 | 1987-03-01 |

| 1999-12-31 | 1999-12-01 |

| 2000-06-04 | 2000-06-01 |

+------------+---------------------------+

TIME_FORMAT() can be used in a similar way. The following example produces time values that have the seconds part set to 00:

mysql>SELECT t1, TIME_FORMAT(t1,'%H:%i:00') FROM time_val;

+----------+----------------------------+

| t1 | TIME_FORMAT(t1,'%H:%i:00') |

+----------+----------------------------+

| 15:00:00 | 15:00:00 |

| 05:01:30 | 05:01:00 |

| 12:30:20 | 12:30:00 |

+----------+----------------------------+

Another way to construct temporal values is to use date-part extraction functions in conjunction with CONCAT(). However, this method often is messier than the DATE_FORMAT() technique just discussed, and it sometimes yields slightly different results:

mysql>SELECT d,

-> CONCAT(YEAR(d),'-',MONTH(d),'-01')

-> FROM date_val;

+------------+------------------------------------+

| d | CONCAT(YEAR(d),'-',MONTH(d),'-01') |

+------------+------------------------------------+

| 1864-02-28 | 1864-2-01 |

| 1900-01-15 | 1900-1-01 |

| 1987-03-05 | 1987-3-01 |

| 1999-12-31 | 1999-12-01 |

| 2000-06-04 | 2000-6-01 |

+------------+------------------------------------+

Note that the month values in some of these dates have only a single digit. To ensure that the month has two digits—as required for ISO format—use LPAD() to add a leading zero as necessary:

mysql>SELECT d,

-> CONCAT(YEAR(d),'-',LPAD(MONTH(d),2,'0'),'-01')

-> FROM date_val;

+------------+------------------------------------------------+

| d | CONCAT(YEAR(d),'-',LPAD(MONTH(d),2,'0'),'-01') |

+------------+------------------------------------------------+

| 1864-02-28 | 1864-02-01 |

| 1900-01-15 | 1900-01-01 |

| 1987-03-05 | 1987-03-01 |

| 1999-12-31 | 1999-12-01 |

| 2000-06-04 | 2000-06-01 |

+------------+------------------------------------------------+

Canonizing Not-Quite-ISO Date Strings shows other ways to solve the problem of producing ISO dates from not-quite-ISO dates.

TIME values can be produced from hours, minutes, and seconds values using methods analogous to those for creating DATE values. For example, to change a TIME value so that its seconds part is 00, extract the hour and minute parts, and then recombine them with CONCAT():

mysql>SELECT t1,

-> CONCAT(LPAD(HOUR(t1),2,'0'),':',LPAD(MINUTE(t1),2,'0'),':00')

-> AS recombined

-> FROM time_val;

+----------+------------+

| t1 | recombined |

+----------+------------+

| 15:00:00 | 15:00:00 |

| 05:01:30 | 05:01:00 |

| 12:30:20 | 12:30:00 |

+----------+------------+

To produce a combined date-and-time value from separate date and time values, simply concatenate them with a space in between:

mysql>SET @d = '2006-02-28';

mysql> SET @t = '13:10:05';

mysql> SELECT @d, @t, CONCAT(@d,' ',@t);

+------------+----------+---------------------+

| @d | @t | CONCAT(@d,' ',@t) |

+------------+----------+---------------------+

| 2006-02-28 | 13:10:05 | 2006-02-28 13:10:05 |

+------------+----------+---------------------+

Converting Between Temporal Data Types and Basic Units

Problem

You have a function temporal value such as a time or date that you want to convert to basic units such as seconds or days. This is often useful or necessary for performing temporal arithmetic operations (Recipes and ).

Solution

The conversion method depends on the type of value to be converted:

§ To convert between time values and seconds, use the TIME_TO_SEC() and SEC_TO_TIME() functions.

§ To convert between date values and days, use the TO_DAYS() and FROM_DAYS() functions.

§ To convert between date-and-time values and seconds, use the UNIX_TIMESTAMP() and FROM_UNIXTIME() functions.

Discussion

The following discussion shows how to convert several types of temporal values to basic units and vice versa.

Converting between times and seconds

TIME values are specialized representations of a simpler unit (seconds), so you can convert back and forth from one to the other using the TIME_TO_SEC() and SEC_TO_TIME() functions.

TIME_TO_SEC() converts a TIME value to the equivalent number of seconds, and SEC_TO_TIME() does the opposite. The following statement demonstrates a simple conversion in both directions:

mysql>SELECT t1,

-> TIME_TO_SEC(t1) AS 'TIME to seconds',

-> SEC_TO_TIME(TIME_TO_SEC(t1)) AS 'TIME to seconds to TIME'

-> FROM time_val;

+----------+-----------------+-------------------------+

| t1 | TIME to seconds | TIME to seconds to TIME |

+----------+-----------------+-------------------------+

| 15:00:00 | 54000 | 15:00:00 |

| 05:01:30 | 18090 | 05:01:30 |

| 12:30:20 | 45020 | 12:30:20 |

+----------+-----------------+-------------------------+

To express time values as minutes, hours, or days, perform the appropriate divisions:

mysql>SELECT t1,

-> TIME_TO_SEC(t1) AS 'seconds',

-> TIME_TO_SEC(t1)/60 AS 'minutes',

-> TIME_TO_SEC(t1)/(60*60) AS 'hours',

-> TIME_TO_SEC(t1)/(24*60*60) AS 'days'

-> FROM time_val;

+----------+---------+----------+---------+--------+

| t1 | seconds | minutes | hours | days |

+----------+---------+----------+---------+--------+

| 15:00:00 | 54000 | 900.0000 | 15.0000 | 0.6250 |

| 05:01:30 | 18090 | 301.5000 | 5.0250 | 0.2094 |

| 12:30:20 | 45020 | 750.3333 | 12.5056 | 0.5211 |

+----------+---------+----------+---------+--------+

Use FLOOR() if you prefer integer values that have no fractional part:

mysql>SELECT t1,

-> TIME_TO_SEC(t1) AS 'seconds',

-> FLOOR(TIME_TO_SEC(t1)/60) AS 'minutes',

-> FLOOR(TIME_TO_SEC(t1)/(60*60)) AS 'hours',

-> FLOOR(TIME_TO_SEC(t1)/(24*60*60)) AS 'days'

-> FROM time_val;

+----------+---------+---------+-------+------+

| t1 | seconds | minutes | hours | days |

+----------+---------+---------+-------+------+

| 15:00:00 | 54000 | 900 | 15 | 0 |

| 05:01:30 | 18090 | 301 | 5 | 0 |

| 12:30:20 | 45020 | 750 | 12 | 0 |

+----------+---------+---------+-------+------+

If you pass TIME_TO_SEC() a date-and-time value, it extracts the time part and discards the date. This provides yet another means of extracting times from DATETIME and TIMESTAMP values (in addition to those already discussed in Extracting Parts of Dates or Times):

mysql>SELECT dt,

-> TIME_TO_SEC(dt) AS 'time part in seconds',

-> SEC_TO_TIME(TIME_TO_SEC(dt)) AS 'time part as TIME'

-> FROM datetime_val;

+---------------------+----------------------+-------------------+

| dt | time part in seconds | time part as TIME |

+---------------------+----------------------+-------------------+

| 1970-01-01 00:00:00 | 0 | 00:00:00 |

| 1987-03-05 12:30:15 | 45015 | 12:30:15 |

| 1999-12-31 09:00:00 | 32400 | 09:00:00 |

| 2000-06-04 15:45:30 | 56730 | 15:45:30 |

+---------------------+----------------------+-------------------+

mysql> SELECT ts,

-> TIME_TO_SEC(ts) AS 'time part in seconds',

-> SEC_TO_TIME(TIME_TO_SEC(ts)) AS 'time part as TIME'

-> FROM timestamp_val;

+---------------------+----------------------+-------------------+

| ts | time part in seconds | time part as TIME |

+---------------------+----------------------+-------------------+

| 1970-01-01 00:00:00 | 0 | 00:00:00 |

| 1987-03-05 12:30:15 | 45015 | 12:30:15 |

| 1999-12-31 09:00:00 | 32400 | 09:00:00 |

| 2000-06-04 15:45:30 | 56730 | 15:45:30 |

+---------------------+----------------------+-------------------+

Converting between dates and days

If you have a date but want a value in days, or vice versa, use the TO_DAYS() and FROM_DAYS() functions. Date-and-time values also can be converted to days if you’re willing to suffer loss of the time part.

TO_DAYS() converts a date to the corresponding number of days, and FROM_DAYS() does the opposite:

mysql>SELECT d,

-> TO_DAYS(d) AS 'DATE to days',

-> FROM_DAYS(TO_DAYS(d)) AS 'DATE to days to DATE'

-> FROM date_val;

+------------+--------------+----------------------+

| d | DATE to days | DATE to days to DATE |

+------------+--------------+----------------------+

| 1864-02-28 | 680870 | 1864-02-28 |

| 1900-01-15 | 693975 | 1900-01-15 |

| 1987-03-05 | 725800 | 1987-03-05 |

| 1999-12-31 | 730484 | 1999-12-31 |

| 2000-06-04 | 730640 | 2000-06-04 |

+------------+--------------+----------------------+

When using TO_DAYS(), it’s best to stick to the advice of the MySQL Reference Manual and avoid DATE values that occur before the beginning of the Gregorian calendar (1582). Changes in the lengths of calendar years and months prior to that date make it difficult to speak meaningfully of what the value of “day 0” might be. This differs from TIME_TO_SEC(), where the correspondence between a TIME value and the resulting seconds value is obvious and has a meaningful reference point of 0 seconds.

If you pass TO_DAYS() a date-and-time value, it extracts the date part and discards the time. This provides another means of extracting dates from DATETIME and TIMESTAMP values (in addition to those already discussed in Extracting Parts of Dates or Times):

mysql>SELECT dt,

-> TO_DAYS(dt) AS 'date part in days',

-> FROM_DAYS(TO_DAYS(dt)) AS 'date part as DATE'

-> FROM datetime_val;

+---------------------+-------------------+-------------------+

| dt | date part in days | date part as DATE |

+---------------------+-------------------+-------------------+

| 1970-01-01 00:00:00 | 719528 | 1970-01-01 |

| 1987-03-05 12:30:15 | 725800 | 1987-03-05 |

| 1999-12-31 09:00:00 | 730484 | 1999-12-31 |

| 2000-06-04 15:45:30 | 730640 | 2000-06-04 |

+---------------------+-------------------+-------------------+

mysql> SELECT ts,

-> TO_DAYS(ts) AS 'date part in days',

-> FROM_DAYS(TO_DAYS(ts)) AS 'date part as DATE'

-> FROM timestamp_val;

+---------------------+-------------------+-------------------+

| ts | date part in days | date part as DATE |

+---------------------+-------------------+-------------------+

| 1970-01-01 00:00:00 | 719528 | 1970-01-01 |

| 1987-03-05 12:30:15 | 725800 | 1987-03-05 |

| 1999-12-31 09:00:00 | 730484 | 1999-12-31 |

| 2000-06-04 15:45:30 | 730640 | 2000-06-04 |

+---------------------+-------------------+-------------------+

Converting between date-and-time values and seconds

For DATETIME or TIMESTAMP values that lie within the range of the TIMESTAMP data type (from the beginning of 1970 through approximately 2037), the UNIX_TIMESTAMP() and FROM_UNIXTIME() functions convert to and from the number of seconds elapsed since the beginning of 1970. The conversion to seconds offers higher precision for date-and-time values than a conversion to days, at the cost of a more limited range of values for which the conversion may be performed (TIME_TO_SEC() is unsuitable for this because it discards the date):

mysql>SELECT dt,

-> UNIX_TIMESTAMP(dt) AS seconds,

-> FROM_UNIXTIME(UNIX_TIMESTAMP(dt)) AS timestamp

-> FROM datetime_val;

+---------------------+-----------+---------------------+

| dt | seconds | timestamp |

+---------------------+-----------+---------------------+

| 1970-01-01 00:00:00 | 21600 | 1970-01-01 00:00:00 |

| 1987-03-05 12:30:15 | 541967415 | 1987-03-05 12:30:15 |

| 1999-12-31 09:00:00 | 946652400 | 1999-12-31 09:00:00 |

| 2000-06-04 15:45:30 | 960151530 | 2000-06-04 15:45:30 |

+---------------------+-----------+---------------------+

The relationship between the “UNIX” in the function names and the fact that the applicable range of values begins with 1970 is that 1970-01-01 00:00:00 UTC marks the “Unix epoch.” The epoch is time zero, or the reference point for measuring time in Unix systems. That being so, you may find it curious that the preceding example shows a UNIX_TIMESTAMP() value of 21600 for the first value in the datetime_val table. What’s going on? Why isn’t it 0? The apparent discrepancy is due to the fact that the MySQL server interprets the UNIX_TIMESTAMP() argument as a value in the client’s local time zone and converts it to UTC. My server is in the U.S. Central time zone, six hours (21600 seconds) west of UTC.

UNIX_TIMESTAMP() can convert DATE values to seconds, too. It treats such values as having an implicit time-of-day part of 00:00:00:

mysql>SELECT

-> CURDATE(),

-> UNIX_TIMESTAMP(CURDATE()),

-> FROM_UNIXTIME(UNIX_TIMESTAMP(CURDATE()))\G

*************************** 1. row ***************************

CURDATE(): 2006-05-30

UNIX_TIMESTAMP(CURDATE()): 1148965200

FROM_UNIXTIME(UNIX_TIMESTAMP(CURDATE())): 2006-05-30 00:00:00

Calculating the Interval Between Two Dates or Times

Problem

You want to know how long it is between two dates or times. That is, you want to know the interval between two temporal values.

Solution

To calculate an interval, either use one of the temporal-difference functions, or convert your values to basic units and take the difference. The allowable functions depend on the types of the values for which you want to know the interval.

Discussion

The following discussion shows several ways to perform interval calculations.

Calculating intervals with temporal-difference functions

To calculate an interval in days between two date values, use the DATEDIFF() function:

mysql>SET @d1 = '2010-01-01', @d2 = '2009-12-01';

mysql> SELECT DATEDIFF(@d1,@d2) AS 'd1 - d2', DATEDIFF(@d2,@d1) AS 'd2 - d1';

+---------+---------+

| d1 - d2 | d2 - d1 |

+---------+---------+

| 31 | -31 |

+---------+---------+

DATEDIFF() also works with date-and-time values, but it ignores the time part. This makes it suitable for producing day intervals for DATE, DATETIME, or TIMESTAMP values.

To calculate an interval between TIME values as another TIME value, use the TIMEDIFF() function:

mysql>SET @t1 = '12:00:00', @t2 = '16:30:00';

mysql> SELECT TIMEDIFF(@t1,@t2) AS 't1 - t2', TIMEDIFF(@t2,@t1) AS 't2 - t1';

+-----------+----------+

| t1 - t2 | t2 - t1 |

+-----------+----------+

| -04:30:00 | 04:30:00 |

+-----------+----------+

TIMEDIFF() also works for date-and-time values. That it, it accepts either time or date-and-time values, but the types of the arguments must match.

A time interval expressed as a TIME value can be broken down into components using the techniques shown in Extracting Parts of Dates or Times. For example, to express a time interval in terms of its constituent hours, minutes, and seconds values, calculate time interval subparts in SQL using the HOUR(), MINUTE(), and SECOND() functions. (Don’t forget that if your intervals may be negative, you need to take that into account.) To determine the components of the interval between the t1 and t2 columns in the time_val table, the following SQL statement does the trick:

mysql>SELECT t1, t2,

-> TIMEDIFF(t2,t1) AS 't2 - t1 as TIME',

-> IF(TIMEDIFF(t2,t1) >= 0,'+','-') AS sign,

-> HOUR(TIMEDIFF(t2,t1)) AS hour,

-> MINUTE(TIMEDIFF(t2,t1)) AS minute,

-> SECOND(TIMEDIFF(t2,t1)) AS second

-> FROM time_val;

+----------+----------+-----------------+------+------+--------+--------+

| t1 | t2 | t2 - t1 as TIME | sign | hour | minute | second |

+----------+----------+-----------------+------+------+--------+--------+

| 15:00:00 | 15:00:00 | 00:00:00 | + | 0 | 0 | 0 |

| 05:01:30 | 02:30:20 | -02:31:10 | - | 2 | 31 | 10 |

| 12:30:20 | 17:30:45 | 05:00:25 | + | 5 | 0 | 25 |

+----------+----------+-----------------+------+------+--------+--------+

If you’re working with date or date-and-time values, the TIMESTAMPDIFF() function provides another way to calculate intervals, and it enables you to specify the units in which intervals should be expressed. It has this syntax:

TIMESTAMPDIFF(unit,val1,val2)

unit is the interval unit and val1 and val2 are the values between which to calculate the interval. With TIMESTAMPDIFF(), you can express an interval many different ways:

mysql>SET @dt1 = '1900-01-01 00:00:00', @dt2 = '1910-01-01 00:00:00';

mysql> SELECT

-> TIMESTAMPDIFF(MINUTE,@dt1,@dt2) AS minutes,

-> TIMESTAMPDIFF(HOUR,@dt1,@dt2) AS hours,

-> TIMESTAMPDIFF(DAY,@dt1,@dt2) AS days,

-> TIMESTAMPDIFF(WEEK,@dt1,@dt2) AS weeks,

-> TIMESTAMPDIFF(YEAR,@dt1,@dt2) AS years;

+---------+-------+------+-------+-------+

| minutes | hours | days | weeks | years |

+---------+-------+------+-------+-------+

| 5258880 | 87648 | 3652 | 521 | 10 |

+---------+-------+------+-------+-------+

The allowable unit specifiers are FRAC_SECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR. Note that each of these unit specifiers is given in singular form, not plural.

Be aware of these properties of TIMESTAMPDIFF():

§ Its value is negative if the first temporal value is greater then the second, which is opposite the order of the arguments for DATEDIFF() and TIMEDIFF().

§ Despite the TIMESTAMP in its name, the TIMESTAMPDIFF() function arguments are not limited to the range of the TIMESTAMP data type.

§ TIMESTAMPDIFF() requires MySQL 5.0 or higher. For older versions of MySQL, use one of the other interval-calculation techniques described in this section.

Calculating intervals using basic units

Another strategy for calculating intervals is to work with basic units such as seconds or days using this strategy:

1. Convert the temporal values that you’re working with to basic units.

2. Take the difference between the values to calculate the interval, also in basic units.

3. If you want the result as a temporal value, convert it from basic units to the appropriate type.

The conversion functions involved in implementing this strategy depend on the types of the values between which you’re calculating the interval:

§ To convert between time values and seconds, use TIME_TO_SEC() and SEC_TO_TIME().

§ To convert between date values and days, use TO_DAYS() and FROM_DAYS().

§ To convert between date-and-time values and seconds, use UNIX_TIMESTAMP() and FROM_UNIXTIME().

For more information about those conversion functions (and limitations on their applicability), see the discussion in Converting Between Temporal Data Types and Basic Units. The following material assumes familiarity with that discussion.

Time interval calculation using basic units

To calculate intervals in seconds between pairs of time values, convert them to seconds with TIME_TO_SEC(), and then take the difference. To express the resulting interval as a TIME value, pass it to SEC_TO_TIME(). The following statement calculates the intervals between the t1 and t2columns of the time_val table, expressing each interval both in seconds and as a TIME value:

mysql>SELECT t1, t2,

-> TIME_TO_SEC(t2) - TIME_TO_SEC(t1) AS 't2 - t1 (in seconds)',

-> SEC_TO_TIME(TIME_TO_SEC(t2) - TIME_TO_SEC(t1)) AS 't2 - t1 (as TIME)'

-> FROM time_val;

+----------+----------+----------------------+-------------------+

| t1 | t2 | t2 - t1 (in seconds) | t2 - t1 (as TIME) |

+----------+----------+----------------------+-------------------+

| 15:00:00 | 15:00:00 | 0 | 00:00:00 |

| 05:01:30 | 02:30:20 | -9070 | -02:31:10 |

| 12:30:20 | 17:30:45 | 18025 | 05:00:25 |

+----------+----------+----------------------+-------------------+

Date or date-and-time interval calculation using basic units

When you calculate an interval between dates by converting both dates to a common unit in relation to a given reference point and take the difference, the range of values that you’re working with determines which conversions are available:

§ DATE, DATETIME, or TIMESTAMP values dating back to 1970-01-0100:00:00 UTC—the date of the Unix epoch—can be converted to seconds elapsed since the epoch. If both dates lie within that range, you can calculate intervals to an accuracy of one second.

§ Older dates from the beginning of the Gregorian calendar (1582) on can be converted to day values and used to compute intervals in days.

§ Dates that begin earlier than either of these reference points present more of a problem. In such cases, you may find that your programming language offers computations that are not available or are difficult to perform in SQL. If so, consider processing date values directly from within your API language. (For example, the Date::Calc and Date::Manip modules are available from CPAN for use within Perl scripts.)

To calculate an interval in days between date or date-and-time values, convert them to days with TO_DAYS(), and take the difference:

mysql>SELECT TO_DAYS('1884-01-01') - TO_DAYS('1883-06-05') AS days;

+------+

| days |

+------+

| 210 |

+------+

For an interval in weeks, do the same thing and divide the result by seven:

mysql>SELECT (TO_DAYS('1884-01-01') - TO_DAYS('1883-06-05')) / 7 AS weeks;

+---------+

| weeks |

+---------+

| 30.0000 |

+---------+

You cannot convert days to months or years by simple division, because those units vary in length. For calculations to yield date intervals expressed in those units, use the TIMESTAMPDIFF() function discussed earlier in this section.

For date-and-time values occurring within the TIMESTAMP range of 1970 to 2037, you can determine intervals to a resolution in seconds using the UNIX_TIMESTAMP() function. For example, the number of seconds between dates that lie two weeks apart can be computed like this:

mysql>SET @dt1 = '1984-01-01 09:00:00';

mysql> SET @dt2 = @dt1 + INTERVAL 14 DAY;

mysql> SELECT UNIX_TIMESTAMP(@dt2) - UNIX_TIMESTAMP(@dt1) AS seconds;

+---------+

| seconds |

+---------+

| 1209600 |

+---------+

To convert the interval in seconds to other units, perform the appropriate arithmetic operation. Seconds are easily converted to minutes, hours, days, or weeks:

mysql>SET @interval = UNIX_TIMESTAMP(@dt2) - UNIX_TIMESTAMP(@dt1);

mysql> SELECT @interval AS seconds,

-> @interval / 60 AS minutes,

-> @interval / (60 * 60) AS hours,

-> @interval / (24 * 60 * 60) AS days,

-> @interval / (7 * 24 * 60 * 60) AS weeks;

+---------+------------+----------+---------+--------+

| seconds | minutes | hours | days | weeks |

+---------+------------+----------+---------+--------+

| 1209600 | 20160.0000 | 336.0000 | 14.0000 | 2.0000 |

+---------+------------+----------+---------+--------+

To produce integer values (no fractional part), use the FLOOR() function, as shown in Converting Between Temporal Data Types and Basic Units. This applies to several of the following examples as well.

For values that occur outside the TIMESTAMP range, you can use an interval calculation method that is more general (but messier):

§ Take the difference in days between the date parts of the values and multiply by 24 × 60 × 60 to convert to seconds.

§ Offset the result by the difference in seconds between the time parts of the values.

Here’s an example, using two date-and-time values that lie a week apart:

mysql>SET @dt1 = '1800-02-14 07:30:00';

mysql> SET @dt2 = @dt1 + INTERVAL 7 DAY;

mysql> SET @interval =

-> ((TO_DAYS(@dt2) - TO_DAYS(@dt1)) * 24*60*60)

-> + TIME_TO_SEC(@dt2) - TIME_TO_SEC(@dt1);

mysql> SELECT @interval AS seconds, SEC_TO_TIME(@interval) AS TIME;

+---------+-----------+

| seconds | TIME |

+---------+-----------+

| 604800 | 168:00:00 |

+---------+-----------+

DO YOU WANT AN INTERVAL OR A SPAN?

When you take a difference between dates (or times), consider whether you want an interval or a span. Taking a difference between dates gives you the interval from one date to the next. If you want to know the range spanned by the two dates, you must add a unit. For example, it’s a three-day interval from 2002-01-01 to 2002-01-04, but together they span a range of four days. If you’re not getting the results you expect from a difference-of-values calculation, consider whether you need to apply an “off-by-one” correction.

Adding Date or Time Values

Problem

You want to add temporal values. For example, you want to add a given number of seconds to a time or determine what the date will be three weeks from today.

Solution

To add date or time values, you have several options:

§ Use one of the temporal-addition functions.

§ Use the + INTERVAL or - INTERVAL operator.

§ Convert the values to basic units, and take the sum.

The allowable functions or operators depend on the types of the values that you want to add.

Discussion

The following discussion shows several ways to add temporal values.

Adding temporal values using temporal-addition functions or operators

To add a time or date-and-time value and a time value, use the ADDTIME() function:

mysql>SET @t1 = '12:00:00', @t2 = '15:30:00';

mysql> SELECT ADDTIME(@t1,@t2);

+------------------+

| ADDTIME(@t1,@t2) |

+------------------+

| 27:30:00 |

+------------------+

mysql> SET @dt = '1984-03-01 12:00:00', @t = '12:00:00';

mysql> SELECT ADDTIME(@dt,@t);

+---------------------+

| ADDTIME(@dt,@t) |

+---------------------+

| 1984-03-02 00:00:00 |

+---------------------+

To add a date or date-and-time value and a time value, use the TIMESTAMP() function:

mysql>SET @d = '1984-03-01', @t = '15:30:00';

mysql> SELECT TIMESTAMP(@d,@t);

+---------------------+

| TIMESTAMP(@d,@t) |

+---------------------+

| 1984-03-01 15:30:00 |

+---------------------+

mysql> SET @dt = '1984-03-01 12:00:00', @t = '12:00:00';

mysql> SELECT TIMESTAMP(@dt,@t);

+---------------------+

| TIMESTAMP(@dt,@t) |

+---------------------+

| 1984-03-02 00:00:00 |

+---------------------+

MySQL also provides the DATE_ADD() and DATE_SUB() functions for adding intervals to dates and subtracting intervals from dates. Each function takes a date (or date-and-time) value d and an interval, expressed using the following syntax:

DATE_ADD(d,INTERVALval unit)

DATE_SUB(d,INTERVAL val unit)

The + INTERVAL and - INTERVAL operators are similar:

d + INTERVALval unit

d - INTERVAL val unit

unit is the interval unit and val is an expression indicating the number of units. Some of the common unit specifiers are SECOND, MINUTE, HOUR, DAY, MONTH, and YEAR. (Check the MySQL Reference Manual for the full list.) Note that each of these unit specifiers is given in singular form, not plural.

Using DATE_ADD() or DATE_SUB(), you can perform date arithmetic operations such as the following:

§ Determine the date three days from today:

§ mysql>SELECT CURDATE(), DATE_ADD(CURDATE(),INTERVAL 3 DAY);

§ +------------+------------------------------------+

§ | CURDATE() | DATE_ADD(CURDATE(),INTERVAL 3 DAY) |

§ +------------+------------------------------------+

§ | 2006-05-22 | 2006-05-25 |

+------------+------------------------------------+

§ Find the date a week ago:

§ mysql>SELECT CURDATE(), DATE_SUB(CURDATE(),INTERVAL 7 DAY);

§ +------------+------------------------------------+

§ | CURDATE() | DATE_SUB(CURDATE(),INTERVAL 7 DAY) |

§ +------------+------------------------------------+

§ | 2006-05-22 | 2006-05-15 |

+------------+------------------------------------+

As of MySQL 5.0, you can use 1 WEEK instead of 7 DAY, but the result is a DATETIME value rather than a DATE value.

§ For questions where you need to know both the date and the time, begin with a DATETIME or TIMESTAMP value. To answer the question, “What time will it be in 60 hours?”, do this:

§ mysql>SELECT NOW(), DATE_ADD(NOW(),INTERVAL 60 HOUR);

§ +---------------------+----------------------------------+

§ | NOW() | DATE_ADD(NOW(),INTERVAL 60 HOUR) |

§ +---------------------+----------------------------------+

§ | 2006-02-04 09:28:10 | 2006-02-06 21:28:10 |

+---------------------+----------------------------------+

§ Some interval specifiers have both date and time parts. The following adds 14.5 hours to the current date and time:

§ mysql>SELECT NOW(), DATE_ADD(NOW(),INTERVAL '14:30' HOUR_MINUTE);

§ +---------------------+----------------------------------------------+

§ | NOW() | DATE_ADD(NOW(),INTERVAL '14:30' HOUR_MINUTE) |

§ +---------------------+----------------------------------------------+

§ | 2006-02-04 09:28:31 | 2006-02-04 23:58:31 |

+---------------------+----------------------------------------------+

Similarly, adding 3 days and 4 hours produces this result:

mysql>SELECT NOW(), DATE_ADD(NOW(),INTERVAL '3 4' DAY_HOUR);

+---------------------+-----------------------------------------+

| NOW() | DATE_ADD(NOW(),INTERVAL '3 4' DAY_HOUR) |

+---------------------+-----------------------------------------+

| 2006-02-04 09:28:38 | 2006-02-07 13:28:38 |

+---------------------+-----------------------------------------+

DATE_ADD() and DATE_SUB() are interchangeable because one is the same as the other with the sign of the interval value flipped. These two calls are equivalent for any date value d:

DATE_ADD(d,INTERVAL -3 MONTH)

DATE_SUB(d,INTERVAL 3 MONTH)

You can also use the + INTERVAL and - INTERVAL operators to perform date interval addition and subtraction:

mysql>SELECT CURDATE(), CURDATE() + INTERVAL 1 YEAR;

+------------+-----------------------------+

| CURDATE() | CURDATE() + INTERVAL 1 YEAR |

+------------+-----------------------------+

| 2006-05-22 | 2007-05-22 |

+------------+-----------------------------+

mysql> SELECT NOW(), NOW() - INTERVAL '1 12' DAY_HOUR;

+---------------------+----------------------------------+

| NOW() | NOW() - INTERVAL '1 12' DAY_HOUR |

+---------------------+----------------------------------+

| 2006-05-22 19:00:50 | 2006-05-21 07:00:50 |

+---------------------+----------------------------------+

An alternative function for adding intervals to date or date-and-time values is TIMESTAMPADD(), available in MySQL 5.0 or higher. Its arguments are similar to those for DATE_ADD(), and, in fact, the following equivalence holds:

TIMESTAMPADD(unit,interval,d) = DATE_ADD(d,INTERVAL interval

unit)

Adding temporal values using basic units

Another way to add intervals to date or date-and-time values is to perform temporal “shifting” via functions that convert to and from basic units. For background information about the applicable functions, see Converting Between Temporal Data Types and Basic Units.

Adding time values using basic units. Adding times with basic units is similar to calculating intervals between times, except that you compute a sum rather than a difference. To add an interval value in seconds to a TIME value, convert the TIME to seconds so that both values are represented in the same units, add the values together, and convert the result back to a TIME. For example, two hours is 7200 seconds (2 × 60 × 60), so the following statement adds two hours to each t1 value in the time_val table:

mysql>SELECT t1,

-> SEC_TO_TIME(TIME_TO_SEC(t1) + 7200) AS 't1 plus 2 hours'

-> FROM time_val;

+----------+-----------------+

| t1 | t1 plus 2 hours |

+----------+-----------------+

| 15:00:00 | 17:00:00 |

| 05:01:30 | 07:01:30 |

| 12:30:20 | 14:30:20 |

+----------+-----------------+

If the interval itself is expressed as a TIME, it too should be converted to seconds before adding the values together. The following example calculates the sum of the two TIME values in each row of the time_val table:

mysql>SELECT t1, t2,

-> TIME_TO_SEC(t1) + TIME_TO_SEC(t2)

-> AS 't1 + t2 (in seconds)',

-> SEC_TO_TIME(TIME_TO_SEC(t1) + TIME_TO_SEC(t2))

-> AS 't1 + t2 (as TIME)'

-> FROM time_val;

+----------+----------+----------------------+-------------------+

| t1 | t2 | t1 + t2 (in seconds) | t1 + t2 (as TIME) |

+----------+----------+----------------------+-------------------+

| 15:00:00 | 15:00:00 | 108000 | 30:00:00 |

| 05:01:30 | 02:30:20 | 27110 | 07:31:50 |

| 12:30:20 | 17:30:45 | 108065 | 30:01:05 |

+----------+----------+----------------------+-------------------+

It’s important to recognize that MySQL TIME values really represent elapsed time, not time of day, so they don’t reset to 0 after reaching 24 hours. You can see this in the first and third output rows from the previous statement. To produce time-of-day values, enforce a 24-hour wraparound using a modulo operation before converting the seconds value back to a TIME value. The number of seconds in a day is 24 × 60 × 60, or 86,400, so to convert any seconds value s to lie within a 24-hour range, use the MOD() function or the % modulo operator like this:

MOD(s,86400)

s % 86400

s MOD 86400

NOTE

The allowable range of a TIME column is -838:59:59 to 838:59:59 (that is, -3020399 to 3020399 seconds). However, the range of TIME expressions can be greater, so when you add times together, you can easily produce a result that lies outside this range and that cannot be stored as is into a TIME column.

The three expressions are equivalent. Applying the first of them to the time calculations from the preceding example produces the following result:

mysql>SELECT t1, t2,

-> MOD(TIME_TO_SEC(t1) + TIME_TO_SEC(t2), 86400)

-> AS 't1 + t2 (in seconds)',

-> SEC_TO_TIME(MOD(TIME_TO_SEC(t1) + TIME_TO_SEC(t2), 86400))

-> AS 't1 + t2 (as TIME)'

-> FROM time_val;

+----------+----------+----------------------+-------------------+

| t1 | t2 | t1 + t2 (in seconds) | t1 + t2 (as TIME) |

+----------+----------+----------------------+-------------------+

| 15:00:00 | 15:00:00 | 21600 | 06:00:00 |

| 05:01:30 | 02:30:20 | 27110 | 07:31:50 |

| 12:30:20 | 17:30:45 | 21665 | 06:01:05 |

+----------+----------+----------------------+-------------------+

Adding to date or date-and-time values using basic units. By converting date or date-and-time values to basic units, you can shift them to produce other dates. For example, to shift a date forward or backward a week (seven days), use TO_DAYS() and FROM_DAYS():

mysql>SET @d = '2006-01-01';

mysql> SELECT @d AS date,

-> FROM_DAYS(TO_DAYS(@d) + 7) AS 'date + 1 week',

-> FROM_DAYS(TO_DAYS(@d) - 7) AS 'date - 1 week';

+------------+---------------+---------------+

| date | date + 1 week | date - 1 week |

+------------+---------------+---------------+

| 2006-01-01 | 2006-01-08 | 2005-12-25 |

+------------+---------------+---------------+

TO_DAYS() also can convert DATETIME or TIMESTAMP values to days, if you don’t mind having it chop off the time part:

mysql>SET @dt = '2006-01-01 12:30:45';

mysql> SELECT @dt AS datetime,

-> FROM_DAYS(TO_DAYS(@dt) + 7) AS 'datetime + 1 week',

-> FROM_DAYS(TO_DAYS(@dt) - 7) AS 'datetime - 1 week';

+---------------------+-------------------+-------------------+

| datetime | datetime + 1 week | datetime - 1 week |

+---------------------+-------------------+-------------------+

| 2006-01-01 12:30:45 | 2006-01-08 | 2005-12-25 |

+---------------------+-------------------+-------------------+

To preserve the time with DATETIME or TIMESTAMP values, use UNIX_TIMESTAMP() and FROM_UNIXTIME() instead. The following statement shifts a DATETIME value forward and backward by an hour (3,600 seconds):

mysql>SET @dt = '2006-01-01 09:00:00';

mysql> SELECT @dt AS datetime,

-> FROM_UNIXTIME(UNIX_TIMESTAMP(@dt) + 3600) AS 'datetime + 1 hour',

-> FROM_UNIXTIME(UNIX_TIMESTAMP(@dt) - 3600) AS 'datetime - 1 hour';

+---------------------+---------------------+---------------------+

| datetime | datetime + 1 hour | datetime - 1 hour |

+---------------------+---------------------+---------------------+

| 2006-01-01 09:00:00 | 2006-01-01 10:00:00 | 2006-01-01 08:00:00 |

+---------------------+---------------------+---------------------+

The preceding technique requires that both your initial value and the resulting value lie in the allowable range for TIMESTAMP values (1970 to approximately 2037).

Calculating Ages

Problem

You want to know how old someone is.

Solution

This is a date-arithmetic problem. It amounts to computing the interval between dates, but with a twist. For an age in years, it’s necessary to account for the relative placement of the start and end dates within the calendar year. For an age in months, it’s also necessary to account for the placement of the months and the days within the month.

Discussion

Age determination is a type of date interval calculation, but you cannot simply compute a difference in days and divide by 365. That doesn’t work because leap years throw off the calculation. (The interval from 1995-03-01 to 1996-02-29 spans 365 days, but is not a year in age terms.) Using 365.25 is slightly more accurate, but still not correct for all dates. Instead, it’s necessary to determine the difference between dates in years and then adjust for the relative location of the dates within the calendar year. (Suppose that Gretchen Smith was born on April 14, 1942. To compute how old Gretchen is now, we must account for where the current date falls within the calendar year: She’s one age up through April 13 of the year, and one year older from April 14 through the end of the year.) This section shows how to perform this kind of calculation to determine ages in units of years or months.

The easiest way to calculate ages is to use the TIMESTAMPDIFF() function because you can pass it a birth date, a current date, and the unit in which you want the age to be expressed:

TIMESTAMPDIFF(unit,birth,current)

TIMESTAMPDIFF() handles the calculations necessary to adjust for differing month and year lengths and relative positions of the dates within the calendar year. Suppose that we have a sibling table that lists the birth dates of Gretchen Smith and her brothers Wilbur and Franz:

+----------+------------+

| name | birth |

+----------+------------+

| Gretchen | 1942-04-14 |

| Wilbur | 1946-11-28 |

| Franz | 1953-03-05 |

+----------+------------+

Using TIMESTAMPDIFF(), we can answer questions such as these:

§ How old are the Smith children today?

§ mysql>SELECT name, birth, CURDATE() AS today,

§ -> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS 'age in years'

§ -> FROM sibling;

§ +----------+------------+------------+--------------+

§ | name | birth | today | age in years |

§ +----------+------------+------------+--------------+

§ | Gretchen | 1942-04-14 | 2006-05-30 | 64 |

§ | Wilbur | 1946-11-28 | 2006-05-30 | 59 |

§ | Franz | 1953-03-05 | 2006-05-30 | 53 |

+----------+------------+------------+--------------+

§ How old were Gretchen and Wilbur when Franz was born?

§ mysql>SELECT name, birth, '1953-03-05' AS 'Franz'' birthday',

§ -> TIMESTAMPDIFF(YEAR,birth,'1953-03-05') AS 'age in years'

§ -> FROM sibling WHERE name != 'Franz';

§ +----------+------------+-----------------+--------------+

§ | name | birth | Franz' birthday | age in years |

§ +----------+------------+-----------------+--------------+

§ | Gretchen | 1942-04-14 | 1953-03-05 | 10 |

§ | Wilbur | 1946-11-28 | 1953-03-05 | 6 |

+----------+------------+-----------------+--------------+

The preceding queries produce ages in years, but you can request other interval units if you like. For example, the current ages of the Smith children in months can be calculated like this:

mysql>SELECT name, birth, CURDATE() AS today,

-> TIMESTAMPDIFF(MONTH,birth,CURDATE()) AS 'age in months'

-> FROM sibling;

+----------+------------+------------+---------------+

| name | birth | today | age in months |

+----------+------------+------------+---------------+

| Gretchen | 1942-04-14 | 2006-05-30 | 769 |

| Wilbur | 1946-11-28 | 2006-05-30 | 714 |

| Franz | 1953-03-05 | 2006-05-30 | 638 |

+----------+------------+------------+---------------+

TIMESTAMPDIFF() requires MySQL 5.0 or higher. For older versions of MySQL, you can calculate ages without it, but as the following discussion shows, it’s necessary to do more work.

In general, given a birth date birth, an age in years on a target date d can be computed by the following logic:

if (d occurs earlier in the year than birth)

age = YEAR(d) - YEAR(birth) - 1

if (d occurs on or later in the year than birth)

age = YEAR(d) - YEAR(birth)

For both cases, the difference-in-years part of the calculation is the same. What distinguishes them is the relative ordering of the dates within the calendar year. However, this ordering cannot be determined with DAYOFYEAR(), because that only works if both dates fall during years with the same number of days. For dates in different years, different calendar days may have the same DAYOFYEAR() value, as the following statement illustrates:

mysql>SELECT DAYOFYEAR('1995-03-01'), DAYOFYEAR('1996-02-29');

+-------------------------+-------------------------+

| DAYOFYEAR('1995-03-01') | DAYOFYEAR('1996-02-29') |

+-------------------------+-------------------------+

| 60 | 60 |

+-------------------------+-------------------------+

The fact that ISO date strings compare naturally in the proper order comes to our rescue here—or more precisely, the fact that the rightmost five characters that represent the month and day also compare properly:

mysql>SELECT RIGHT('1995-03-01',5), RIGHT('1996-02-29',5);

+-----------------------+-----------------------+

| RIGHT('1995-03-01',5) | RIGHT('1996-02-29',5) |

+-----------------------+-----------------------+

| 03-01 | 02-29 |

+-----------------------+-----------------------+

mysql> SELECT IF('02-29' < '03-01','02-29','03-01') AS earliest;

+----------+

| earliest |

+----------+

| 02-29 |

+----------+

This means that you can perform the “earlier-in-year” test for two dates, d1 and d2, like this:

RIGHT(d2,5) < RIGHT(d1,5)

The expression evaluates to 1 or 0, depending on the result of the test, so the result of the < comparison can be used to perform an age-in-years calculation:

YEAR(d2) - YEAR(d1) - (RIGHT(d2,5) < RIGHT(d1,5))

If you want to make it more obvious what the comparison result evaluates to, wrap it in an IF() function that explicitly returns 1 or 0:

YEAR(d2) - YEAR(d1) - IF(RIGHT(d2,5) < RIGHT(d1,5),1,0)

The following statement demonstrates how this formula works to calculate an age as of the beginning of 1975 for someone born on 1965-03-01. It shows the unadjusted age difference in years, the adjustment value, and the final age:

mysql>SET @birth = '1965-03-01';

mysql> SET @target = '1975-01-01';

mysql> SELECT @birth, @target,

-> YEAR(@target) - YEAR(@birth) AS 'difference',

-> IF(RIGHT(@target,5) < RIGHT(@birth,5),1,0) AS 'adjustment',

-> YEAR(@target) - YEAR(@birth)

-> - IF(RIGHT(@target,5) < RIGHT(@birth,5),1,0)

-> AS 'age';

+------------+------------+------------+------------+------+

| @birth | @target | difference | adjustment | age |

+------------+------------+------------+------------+------+

| 1965-03-01 | 1975-01-01 | 10 | 1 | 9 |

+------------+------------+------------+------------+------+

Let’s try the age-in-years formula with the sibling table. We can check the formula using the same questions that we answered earlier with TIMESTAMPDIFF(). The formula produces answers for questions such as the following:

§ How old are the Smith children today?

§ mysql>SELECT name, birth, CURDATE() AS today,

§ -> YEAR(CURDATE()) - YEAR(birth)

§ -> - IF(RIGHT(CURDATE(),5) < RIGHT(birth,5),1,0)

§ -> AS 'age in years'

§ -> FROM sibling;

§ +----------+------------+------------+--------------+

§ | name | birth | today | age in years |

§ +----------+------------+------------+--------------+

§ | Gretchen | 1942-04-14 | 2006-05-30 | 64 |

§ | Wilbur | 1946-11-28 | 2006-05-30 | 59 |

§ | Franz | 1953-03-05 | 2006-05-30 | 53 |

+----------+------------+------------+--------------+

§ How old were Gretchen and Wilbur when Franz was born?

§ mysql>SELECT name, birth, '1953-03-05' AS 'Franz'' birthday',

§ -> YEAR('1953-03-05') - YEAR(birth)

§ -> - IF(RIGHT('1953-03-05',5) < RIGHT(birth,5),1,0)

§ -> AS 'age in years'

§ -> FROM sibling WHERE name != 'Franz';

§ +----------+------------+-----------------+--------------+

§ | name | birth | Franz' birthday | age in years |

§ +----------+------------+-----------------+--------------+

§ | Gretchen | 1942-04-14 | 1953-03-05 | 10 |

§ | Wilbur | 1946-11-28 | 1953-03-05 | 6 |

+----------+------------+-----------------+--------------+

When performing calculations of this nature, be sure to remember that for comparisons on the MM-DD part of date strings to yield correct results, you must use ISO values like 1987-07-01 and not close-to-ISO values like 1987-7-1. For example, the following comparison produces a result that is correct in lexical terms but incorrect in temporal terms:

mysql>SELECT RIGHT('1987-7-1',5) < RIGHT('1987-10-01',5);

+---------------------------------------------+

| RIGHT('1987-7-1',5) < RIGHT('1987-10-01',5) |

+---------------------------------------------+

| 0 |

+---------------------------------------------+

The absence of leading zeros in the month and day parts of the first date makes the substring-based comparison fail. If you need to canonize not-quite-ISO date values, see Canonizing Not-Quite-ISO Date Strings.

The formula for calculating ages in months is similar to that for ages in years, except that we multiply the years difference by 12, add the months difference, and adjust for the relative day-in-month values of the two dates. In this case, we need to use the month and day part of each date separately, so we may as well extract them directly using MONTH() and DAYOFMONTH() rather than performing a comparison on the MM-DD part of the date strings. The current ages of the Smith children in months thus can be calculated like this:

mysql>SELECT name, birth, CURDATE() AS today,

-> (YEAR(CURDATE()) - YEAR(birth)) * 12

-> + (MONTH(CURDATE()) - MONTH(birth))

-> - IF(DAYOFMONTH(CURDATE()) < DAYOFMONTH(birth),1,0)

-> AS 'age in months'

-> FROM sibling;

+----------+------------+------------+---------------+

| name | birth | today | age in months |

+----------+------------+------------+---------------+

| Gretchen | 1942-04-14 | 2006-05-30 | 769 |

| Wilbur | 1946-11-28 | 2006-05-30 | 714 |

| Franz | 1953-03-05 | 2006-05-30 | 638 |

+----------+------------+------------+---------------+

Shifting a Date-and-Time Value to a Different Time Zone

Problem

You have a date-and-time value, but need to know what it would be in a different time zone. For example, you’re having a teleconference with people in different parts of the world and you need to tell them the meeting time in their local time zones.

Solution

Use the CONVERT_TZ() function.

Discussion

The CONVERT_TZ() function takes three arguments: a date-and-time value and two time zone indicators. The function interprets the date-and-time value as a value in the first time zone and produces a result consisting of the value shifted into the second time zone.

Suppose that I live in Chicago, Illinois in the U.S., and that I need to have a meeting with people in several other parts of the world. The following table shows the location of each meeting participant and the time zone name for each.

If the meeting is to take place at 9 AM local time for me on November 23, 2006, what time will that be for the other participants? The following statement uses CONVERT_TZ() to calculate the local times for each time zone:

mysql>SET @dt = '2006-11-23 09:00:00';

mysql> SELECT @dt AS Chicago,

-> CONVERT_TZ(@dt,'US/Central','Europe/Berlin') AS Berlin,

-> CONVERT_TZ(@dt,'US/Central','Europe/London') AS London,

-> CONVERT_TZ(@dt,'US/Central','America/Edmonton') AS Edmonton,

-> CONVERT_TZ(@dt,'US/Central','Australia/Brisbane') AS Brisbane\G

*************************** 1. row ***************************

Chicago: 2006-11-23 09:00:00

Berlin: 2006-11-23 16:00:00

London: 2006-11-23 15:00:00

Edmonton: 2006-11-23 08:00:00

Brisbane: 2006-11-24 01:00:00

Let’s hope the Brisbane participant doesn’t mind being up after midnight.

The preceding example uses time zone names, so it requires that you have the time zone tables in the mysql database initialized with support for named time zones. (See the MySQL Reference Manual for information about setting up the time zone tables.) If you can’t use named time zones, you can specify the zones in terms of their numeric relationship to UTC. This can be a little trickier because you might need to account for daylight saving time. The corresponding statement with numeric time zones looks like this:

mysql>SELECT @dt AS Chicago,

-> CONVERT_TZ(@dt,'-06:00','+01:00') AS Berlin,

-> CONVERT_TZ(@dt,'-06:00','+00:00') AS London,

-> CONVERT_TZ(@dt,'-06:00','-07:00') AS Edmonton,

-> CONVERT_TZ(@dt,'-06:00','+10:00') AS Brisbane\G

*************************** 1. row ***************************

Chicago: 2006-11-23 09:00:00

Berlin: 2006-11-23 16:00:00

London: 2006-11-23 15:00:00

Edmonton: 2006-11-23 08:00:00

Brisbane: 2006-11-24 01:00:00

Finding the First Day, Last Day, or Length of a Month

Problem

Given a date, you want to determine the date for the first or last day of the month in which the date occurs, or the first or last day for the month n months away. A related problem is to determine the number of days in a month.

Solution

To determine the date for the first day in a month, use date shifting (an application of date arithmetic). To determine the date for the last day, use the LAST_DAY() function. To determine the number of days in a month, find the date for the last day and use it as the argument toDAYOFMONTH().

Discussion

Sometimes you have a reference date and want to reach a target date that doesn’t have a fixed relationship to the reference date. For example, the first or last days of the current month aren’t a fixed number of days from the current date.

To find the first day of the month for a given date, shift the date back by one fewer days than its DAYOFMONTH() value:

mysql>SELECT d, DATE_SUB(d,INTERVAL DAYOFMONTH(d)-1 DAY) AS '1st of month'

-> FROM date_val;

+------------+--------------+

| d | 1st of month |

+------------+--------------+

| 1864-02-28 | 1864-02-01 |

| 1900-01-15 | 1900-01-01 |

| 1987-03-05 | 1987-03-01 |

| 1999-12-31 | 1999-12-01 |

| 2000-06-04 | 2000-06-01 |

+------------+--------------+

In the general case, to find the first of the month for any month n months away from a given date, calculate the first of the month for the date, and then shift the result by n months:

DATE_ADD(DATE_SUB(d,INTERVAL DAYOFMONTH(d)-1 DAY),INTERVALn MONTH)

For example, to find the first day of the previous and following months relative to a given date, n is -1 and 1:

mysql>SELECT d,

-> DATE_ADD(DATE_SUB(d,INTERVAL DAYOFMONTH(d)-1 DAY),INTERVAL -1 MONTH)

-> AS '1st of previous month',

-> DATE_ADD(DATE_SUB(d,INTERVAL DAYOFMONTH(d)-1 DAY),INTERVAL 1 MONTH)

-> AS '1st of following month'

-> FROM date_val;

+------------+-----------------------+------------------------+

| d | 1st of previous month | 1st of following month |

+------------+-----------------------+------------------------+

| 1864-02-28 | 1864-01-01 | 1864-03-01 |

| 1900-01-15 | 1899-12-01 | 1900-02-01 |

| 1987-03-05 | 1987-02-01 | 1987-04-01 |

| 1999-12-31 | 1999-11-01 | 2000-01-01 |

| 2000-06-04 | 2000-05-01 | 2000-07-01 |

+------------+-----------------------+------------------------+

It’s easier to find the last day of the month for a given date because there is a function for it:

mysql>SELECT d, LAST_DAY(d) AS 'last of month'

-> FROM date_val;

+------------+---------------+

| d | last of month |

+------------+---------------+

| 1864-02-28 | 1864-02-29 |

| 1900-01-15 | 1900-01-31 |

| 1987-03-05 | 1987-03-31 |

| 1999-12-31 | 1999-12-31 |

| 2000-06-04 | 2000-06-30 |

+------------+---------------+

For the general case, to find the last of the month for any month n months away from a given date, shift the date by that many months first and then pass it to LAST_DAY():

LAST_DAY(DATE_ADD(d,INTERVALn MONTH))

For example, to find the last day of the previous and following months relative to a given date, n is -1 and 1:

mysql>SELECT d,

-> LAST_DAY(DATE_ADD(d,INTERVAL -1 MONTH))

-> AS 'last of previous month',

-> LAST_DAY(DATE_ADD(d,INTERVAL 1 MONTH))

-> AS 'last of following month'

-> FROM date_val;

+------------+------------------------+-------------------------+

| d | last of previous month | last of following month |

+------------+------------------------+-------------------------+

| 1864-02-28 | 1864-01-31 | 1864-03-31 |

| 1900-01-15 | 1899-12-31 | 1900-02-28 |

| 1987-03-05 | 1987-02-28 | 1987-04-30 |

| 1999-12-31 | 1999-11-30 | 2000-01-31 |

| 2000-06-04 | 2000-05-31 | 2000-07-31 |

+------------+------------------------+-------------------------+

To find the length of a month in days, determine the date of its last day with LAST_DAY(), and then use DAYOFMONTH() to extract the day-of-month component from the result:

mysql>SELECT d, DAYOFMONTH(LAST_DAY(d)) AS 'days in month' FROM date_val;

+------------+---------------+

| d | days in month |

+------------+---------------+

| 1864-02-28 | 29 |

| 1900-01-15 | 31 |

| 1987-03-05 | 31 |

| 1999-12-31 | 31 |

| 2000-06-04 | 30 |

+------------+---------------+

See Also

Performing Leap Year Calculations later in this chapter discusses how to calculate month lengths from within a program without using SQL. (The trick is that you must account for leap years.)

Calculating Dates by Substring Replacement

Problem

Given a date, you want to produce another date from it when you know that the two dates share some components in common.

Solution

Treat a date or time value as a string, and perform direct replacement on parts of the string.

Discussion

In some cases, you can use substring replacement to calculate dates without performing any date arithmetic. For example, you can use string operations to produce the first-of-month value for a given date by replacing the day component with 01. You can do this either with DATE_FORMAT()or with CONCAT():

mysql>SELECT d,

-> DATE_FORMAT(d,'%Y-%m-01') AS method1,

-> CONCAT(YEAR(d),'-',LPAD(MONTH(d),2,'0'),'-01') AS method2

-> FROM date_val;

+------------+------------+------------+

| d | method1 | method2 |

+------------+------------+------------+

| 1864-02-28 | 1864-02-01 | 1864-02-01 |

| 1900-01-15 | 1900-01-01 | 1900-01-01 |

| 1987-03-05 | 1987-03-01 | 1987-03-01 |

| 1999-12-31 | 1999-12-01 | 1999-12-01 |

| 2000-06-04 | 2000-06-01 | 2000-06-01 |

+------------+------------+------------+

The string replacement technique can also be used to produce dates with a specific position within the calendar year. For New Year’s Day (January 1), replace the month and day with 01:

mysql>SELECT d,

-> DATE_FORMAT(d,'%Y-01-01') AS method1,

-> CONCAT(YEAR(d),'-01-01') AS method2

-> FROM date_val;

+------------+------------+------------+

| d | method1 | method2 |

+------------+------------+------------+

| 1864-02-28 | 1864-01-01 | 1864-01-01 |

| 1900-01-15 | 1900-01-01 | 1900-01-01 |

| 1987-03-05 | 1987-01-01 | 1987-01-01 |

| 1999-12-31 | 1999-01-01 | 1999-01-01 |

| 2000-06-04 | 2000-01-01 | 2000-01-01 |

+------------+------------+------------+

For Christmas, replace the month and day with 12 and 25:

mysql>SELECT d,

-> DATE_FORMAT(d,'%Y-12-25') AS method1,

-> CONCAT(YEAR(d),'-12-25') AS method2

-> FROM date_val;

+------------+------------+------------+

| d | method1 | method2 |

+------------+------------+------------+

| 1864-02-28 | 1864-12-25 | 1864-12-25 |

| 1900-01-15 | 1900-12-25 | 1900-12-25 |

| 1987-03-05 | 1987-12-25 | 1987-12-25 |

| 1999-12-31 | 1999-12-25 | 1999-12-25 |

| 2000-06-04 | 2000-12-25 | 2000-12-25 |

+------------+------------+------------+

To perform the same operation for Christmas in other years, combine string replacement with date shifting. The following statement shows two ways to determine the date for Christmas two years hence. The first method finds Christmas for this year, and then shifts it two years forward. The second shifts the current date forward two years, and then finds Christmas in the resulting year:

mysql>SELECT CURDATE(),

-> DATE_ADD(DATE_FORMAT(CURDATE(),'%Y-12-25'),INTERVAL 2 YEAR)

-> AS method1,

-> DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL 2 YEAR),'%Y-12-25')

-> AS method2;

+------------+------------+------------+

| CURDATE() | method1 | method2 |

+------------+------------+------------+

| 2006-05-22 | 2008-12-25 | 2008-12-25 |

+------------+------------+------------+

Finding the Day of the Week for a Date

Problem

You want to know the day of the week on which a date falls.

Solution

Use the DAYNAME() function.

Discussion

To determine the name of the day of the week for a given date, use DAYNAME():

mysql>SELECT CURDATE(), DAYNAME(CURDATE());

+------------+--------------------+

| CURDATE() | DAYNAME(CURDATE()) |

+------------+--------------------+

| 2006-05-22 | Monday |

+------------+--------------------+

DAYNAME() is often useful in conjunction with other date-related techniques. For example, to determine the day of the week for the first of the month, use the first-of-month expression from Finding the First Day, Last Day, or Length of a Month as the argument to DAYNAME():

mysql>SET @d = CURDATE();

mysql> SET @first = DATE_SUB(@d,INTERVAL DAYOFMONTH(@d)-1 DAY);

mysql> SELECT @d AS 'starting date',

-> @first AS '1st of month date',

-> DAYNAME(@first) AS '1st of month day';

+---------------+-------------------+------------------+

| starting date | 1st of month date | 1st of month day |

+---------------+-------------------+------------------+

| 2006-05-22 | 2006-05-01 | Monday |

+---------------+-------------------+------------------+

Finding Dates for Any Weekday of a Given Week

Problem

You want to compute the date of some weekday for the week in which a given date lies. For example, suppose that you want to know the date of the Tuesday that falls in the same week as 2006-07-09.

Solution

This is an application of date shifting. Figure out the number of days between the starting weekday of the given date and the desired day, and shift the date by that many days.

Discussion

This section and the next describe how to convert one date to another when the target date is specified in terms of days of the week. To solve such problems, you need to know day-of-week values. Suppose you begin with a target date of 2006-07-09. If you want to know what date it is on Tuesday of the week in which that date lies, the calculation depends on what weekday it is. If it’s a Monday, you add a day to produce 2006-07-10, but if it’s a Wednesday, you subtract a day to produce 2006-07-08.

MySQL provides two functions that are useful here. DAYOFWEEK() treats Sunday as the first day of the week and returns 1 through 7 for Sunday through Saturday. WEEKDAY() treats Monday as the first day of the week and returns 0 through 6 for Monday through Sunday. (The examples shown here use DAYOFWEEK().) Another kind of day-of-week operation involves determining the name of the day. DAYNAME() can be used for that.

Calculations that determine one day of the week from another depend on the day you start from as well as the day you want to reach. I find it easiest to shift the reference date first to a known point relative to the beginning of the week, and then shift forward:

§ Shift the reference date back by its DAYOFWEEK() value, which always produces the date for the Saturday preceding the week.

§ Shift the Saturday date by one day to reach the Sunday date, by two days to reach the Monday date, and so forth.

In SQL, those operations can be expressed as follows for a date d, where n is 1 through 7 to produce the dates for Sunday through Saturday:

DATE_ADD(DATE_SUB(d,INTERVAL DAYOFWEEK(d) DAY),INTERVALn DAY)

That expression splits the “shift back to Saturday” and “shift forward” phases into separate operations, but because the intervals for both DATE_SUB() and DATE_ADD() are in days, the expression can be simplified into a single DATE_ADD() call:

DATE_ADD(d,INTERVALn-DAYOFWEEK(d) DAY)

If we apply this formula to the dates in our date_val table, using an n of 1 for Sunday and 7 for Saturday to find the first and last days of the week, we get this result:

mysql>SELECT d, DAYNAME(d) AS day,

-> DATE_ADD(d,INTERVAL 1-DAYOFWEEK(d) DAY) AS Sunday,

-> DATE_ADD(d,INTERVAL 7-DAYOFWEEK(d) DAY) AS Saturday

-> FROM date_val;

+------------+----------+------------+------------+

| d | day | Sunday | Saturday |

+------------+----------+------------+------------+

| 1864-02-28 | Sunday | 1864-02-28 | 1864-03-05 |

| 1900-01-15 | Monday | 1900-01-14 | 1900-01-20 |

| 1987-03-05 | Thursday | 1987-03-01 | 1987-03-07 |

| 1999-12-31 | Friday | 1999-12-26 | 2000-01-01 |

| 2000-06-04 | Sunday | 2000-06-04 | 2000-06-10 |

+------------+----------+------------+------------+

If you want to know the date of some weekday in a week relative to that of the target date, modify the preceding procedure a bit. First, determine the date of the desired weekday in the target date. Then shift the result into the desired week.

Calculating the date for a day of the week in some other week is a problem that breaks down into a day-within-week shift (using the formula just given) plus a week shift. These operations can be done in either order because the amount of shift within the week is the same whether or not you shift the reference date into a different week first. For example, to calculate Wednesday of a week by the preceding formula, n is 4. To compute the date for Wednesday two weeks ago, you can perform the day-within-week shift first, like this:

mysql>SET @target =

-> DATE_SUB(DATE_ADD(CURDATE(),INTERVAL 4-DAYOFWEEK(CURDATE()) DAY),

-> INTERVAL 14 DAY);

mysql> SELECT CURDATE(), @target, DAYNAME(@target);

+------------+------------+------------------+

| CURDATE() | @target | DAYNAME(@target) |

+------------+------------+------------------+

| 2006-05-22 | 2006-05-10 | Wednesday |

+------------+------------+------------------+

Or you can perform the week shift first:

mysql>SET @target =

-> DATE_ADD(DATE_SUB(CURDATE(),INTERVAL 14 DAY),

-> INTERVAL 4-DAYOFWEEK(CURDATE()) DAY);

mysql> SELECT CURDATE(), @target, DAYNAME(@target);

+------------+------------+------------------+

| CURDATE() | @target | DAYNAME(@target) |

+------------+------------+------------------+

| 2006-05-22 | 2006-05-10 | Wednesday |

+------------+------------+------------------+

Some applications need to determine dates such as the n-th instance of particular weekdays. For example, if you administer a payroll where paydays are the second and fourth Thursdays of each month, you’d need to know what those dates are. One way to do this for any given month is to begin with the first-of-month date and shift it forward. It’s easy enough to shift the date to the Thursday in that week; the trick is to figure out how many weeks forward to shift the result to reach the second and fourth Thursdays. If the first of the month occurs on any day from Sunday through Thursday, you shift forward one week to reach the second Thursday. If the first of the month occurs on Friday or later, you shift forward by two weeks. The fourth Thursday is of course two weeks after that.

The following Perl code implements this logic to find all paydays in the year 2007. It runs a loop that constructs the first-of-month date for the months of the year. For each month, it issues a statement that determines the dates of the second and fourth Thursdays:

my $year = 2007;

print "MM/CCYY 2nd Thursday 4th Thursday\n";

foreach my $month (1..12)

{

my $first = sprintf ("%04d-%02d-01", $year, $month);

my ($thu2, $thu4) = $dbh->selectrow_array (qq{

SELECT

DATE_ADD(

DATE_ADD(?,INTERVAL 5-DAYOFWEEK(?) DAY),

INTERVAL IF(DAYOFWEEK(?) <= 5, 7, 14) DAY),

DATE_ADD(

DATE_ADD(?,INTERVAL 5-DAYOFWEEK(?) DAY),

INTERVAL IF(DAYOFWEEK(?) <= 5, 21, 28) DAY)

}, undef, $first, $first, $first, $first, $first, $first);

printf "%02d/%04d %s %s\n", $month, $year, $thu2, $thu4;

}

The output from the program looks like this:

MM/CCYY 2nd Thursday 4th Thursday

01/2007 2007-01-11 2007-01-25

02/2007 2007-02-08 2007-02-22

03/2007 2007-03-08 2007-03-22

04/2007 2007-04-12 2007-04-26

05/2007 2007-05-10 2007-05-24

06/2007 2007-06-14 2007-06-28

07/2007 2007-07-12 2007-07-26

08/2007 2007-08-09 2007-08-23

09/2007 2007-09-13 2007-09-27

10/2007 2007-10-11 2007-10-25

11/2007 2007-11-08 2007-11-22

12/2007 2007-12-13 2007-12-27

Performing Leap Year Calculations

Problem

You need to perform a date calculation that must account for leap years. For example, the length of a month or a year depends on knowing whether the date falls in a leap year.

Solution

Know how to test whether a year is a leap year, and factor the result into your calculation.

Discussion

Date calculations are complicated by the fact that months don’t all have the same number of days, and an additional headache is that February has an extra day during leap years. This recipe shows how to determine whether any given date falls within a leap year and how to take leap years into account when determining the length of a year or month.

Determining whether a date occurs in a leap year

To determine whether a date d falls within a leap year, obtain the year component using YEAR() and test the result. The common rule-of-thumb test for leap years is “divisible by four,” which you can test using the % modulo operator like this:

YEAR(d) % 4 = 0

However, that test is not technically correct. (For example, the year 1900 is divisible by four, but is not a leap year.) For a year to qualify as a leap year, it must satisfy both of the following constraints:

§ The year must be divisible by four.

§ The year cannot be divisible by 100, unless it is also divisible by 400.

The meaning of the second constraint is that turn-of-century years are not leap years, except every fourth century. In SQL, you can express these conditions as follows:

(YEAR(d) % 4 = 0) AND ((YEAR(d) % 100 != 0) OR (YEAR(d) % 400 = 0))

Running our date_val table through both the rule-of-thumb leap-year test and the complete test produces the following results:

mysql>SELECT

-> d,

-> YEAR(d) % 4 = 0

-> AS 'rule-of-thumb test',

-> (YEAR(d) % 4 = 0) AND ((YEAR(d) % 100 != 0) OR (YEAR(d) % 400 = 0))

-> AS 'complete test'

-> FROM date_val;

+------------+--------------------+---------------+

| d | rule-of-thumb test | complete test |

+------------+--------------------+---------------+

| 1864-02-28 | 1 | 1 |

| 1900-01-15 | 1 | 0 |

| 1987-03-05 | 0 | 0 |

| 1999-12-31 | 0 | 0 |

| 2000-06-04 | 1 | 1 |

+------------+--------------------+---------------+

As you can see, the two tests don’t always produce the same result. In particular, the rule-of-thumb test fails for the year 1900; the complete test result is correct because it accounts for the turn-of-century constraint.

Note that because the complete leap-year test needs to check the century, it requires four-digit year values. Two-digit years are ambiguous with respect to the century, making it impossible to assess the turn-of-century constraint.

If you’re working with date values within a program, you can perform leap-year tests with your API language rather than at the SQL level. Pull off the first four digits of the date string to get the year, and then test it. If the language performs automatic string-to-number conversion of the year value, this is easy. Otherwise, you must explicitly convert the year value to numeric form before testing it.

Perl, PHP:

$year = substr ($date, 0, 4);

$is_leap = ($year % 4 == 0) && ($year % 100 != 0 || $year % 400 == 0);

Ruby:

year = date[0..3].to_i

is_leap = (year.modulo(4) == 0) &&

(year.modulo(100) != 0 || year.modulo(400) == 0)

Python:

year = int (date[0:4])

is_leap = (year % 4 == 0) and (year % 100 != 0 or year % 400 == 0)

Java:

int year = Integer.valueOf (date.substring (0, 4)).intValue ();

boolean is_leap = (year % 4 == 0) && (year % 100 != 0 || year % 400 == 0);

Using leap year tests for year-length calculations

Years are usually 365 days long, but leap years have an extra day. To determine the length of a year in which a date falls, you can use one of the leap year tests just shown to figure out whether to add a day:

$year = substr ($date, 0, 4);

$is_leap = ($year % 4 == 0) && ($year % 100 != 0 || $year % 400 == 0);

$days_in_year = ($is_leap ? 366 : 365);

To compute a year’s length in SQL, compute the date of the last day of the year and pass it to DAYOFYEAR():

mysql>SET @d = '2006-04-13';

mysql> SELECT DAYOFYEAR(DATE_FORMAT(@d,'%Y-12-31'));

+---------------------------------------+

| DAYOFYEAR(DATE_FORMAT(@d,'%Y-12-31')) |

+---------------------------------------+

| 365 |

+---------------------------------------+

mysql> SET @d = '2008-04-13';

mysql> SELECT DAYOFYEAR(DATE_FORMAT(@d,'%Y-12-31'));

+---------------------------------------+

| DAYOFYEAR(DATE_FORMAT(@d,'%Y-12-31')) |

+---------------------------------------+

| 366 |

+---------------------------------------+

Using leap year tests for month-length calculations

In Finding the First Day, Last Day, or Length of a Month, we discussed how to determine the number of days in a month by using the LAST_DAY() function in SQL statements.

Within an API language, you can write a non-SQL-based function that, given an ISO-format date argument, returns the number of days in the month during which the date occurs. This is straightforward except for February, where the function must return 29 or 28 depending on whether the year is a leap year. Here’s a Ruby version:

def days_in_month(date)

year = date[0..3].to_i

month = date[5..6].to_i # month, 1-based

days_in_month = [31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31]

days = days_in_month[month-1]

is_leap = (year.modulo(4) == 0) &&

(year.modulo(100) != 0 || year.modulo(400) == 0)

# add a day for Feb of leap years

days += 1 if month == 2 && is_leap

return days

end

Canonizing Not-Quite-ISO Date Strings

Problem

A date is in a format that’s close to but not exactly ISO format.

Solution

Canonize the date by passing it to a function that always returns an ISO-format date result.

Discussion

Earlier in the chapter (Synthesizing Dates or Times from Component Values), we ran into the problem that synthesizing dates with CONCAT() may produce values that are not quite in ISO format. For example, the following statement produces first-of-month values in which the month part may have only a single digit:

mysql>SELECT d, CONCAT(YEAR(d),'-',MONTH(d),'-01') FROM date_val;

+------------+------------------------------------+

| d | CONCAT(YEAR(d),'-',MONTH(d),'-01') |

+------------+------------------------------------+

| 1864-02-28 | 1864-2-01 |

| 1900-01-15 | 1900-1-01 |

| 1987-03-05 | 1987-3-01 |

| 1999-12-31 | 1999-12-01 |

| 2000-06-04 | 2000-6-01 |

+------------+------------------------------------+

In that section, a technique using LPAD() was shown for making sure the month values have two digits:

mysql>SELECT d, CONCAT(YEAR(d),'-',LPAD(MONTH(d),2,'0'),'-01') FROM date_val;

+------------+------------------------------------------------+

| d | CONCAT(YEAR(d),'-',LPAD(MONTH(d),2,'0'),'-01') |

+------------+------------------------------------------------+

| 1864-02-28 | 1864-02-01 |

| 1900-01-15 | 1900-01-01 |

| 1987-03-05 | 1987-03-01 |

| 1999-12-31 | 1999-12-01 |

| 2000-06-04 | 2000-06-01 |

+------------+------------------------------------------------+

Another way to standardize a close-to-ISO date is to use it in an expression that produces an ISO date result. For a date d, any of the following expressions will do:

DATE_ADD(d,INTERVAL 0 DAY)

d + INTERVAL 0 DAY

FROM_DAYS(TO_DAYS(d))

STR_TO_DATE(d,'%Y-%m-%d')

For example, the non-ISO results from the CONCAT() operation can be converted into ISO format several different ways as follows:

mysql>SELECT

-> CONCAT(YEAR(d),'-',MONTH(d),'-01') AS 'non-ISO',

-> DATE_ADD(CONCAT(YEAR(d),'-',MONTH(d),'-01'),INTERVAL 0 DAY) AS 'ISO 1',

-> CONCAT(YEAR(d),'-',MONTH(d),'-01') + INTERVAL 0 DAY AS 'ISO 2',

-> FROM_DAYS(TO_DAYS(CONCAT(YEAR(d),'-',MONTH(d),'-01'))) AS 'ISO 3',

-> STR_TO_DATE(CONCAT(YEAR(d),'-',MONTH(d),'-01'),'%Y-%m-%d') AS 'ISO 4'

-> FROM date_val;

+------------+------------+------------+------------+------------+

| non-ISO | ISO 1 | ISO 2 | ISO 3 | ISO 4 |

+------------+------------+------------+------------+------------+

| 1864-2-01 | 1864-02-01 | 1864-02-01 | 1864-02-01 | 1864-02-01 |

| 1900-1-01 | 1900-01-01 | 1900-01-01 | 1900-01-01 | 1900-01-01 |

| 1987-3-01 | 1987-03-01 | 1987-03-01 | 1987-03-01 | 1987-03-01 |

| 1999-12-01 | 1999-12-01 | 1999-12-01 | 1999-12-01 | 1999-12-01 |

| 2000-6-01 | 2000-06-01 | 2000-06-01 | 2000-06-01 | 2000-06-01 |

+------------+------------+------------+------------+------------+

See Also

Chapter 10 discusses leap year calculations in the context of date validation.

Treating Dates or Times as Numbers

Problem

You want to treat a temporal string as a number.

Solution

Perform a string-to-number conversion.

Discussion

In many cases, it is possible in MySQL to treat date and time values as numbers. This can sometimes be useful if you want to perform an arithmetic operation on the value. To force conversion of a temporal value to numeric form, add zero or use it in a numeric context:

mysql>SELECT t1,

-> t1+0 AS 't1 as number',

-> FLOOR(t1) AS 't1 as number',

-> FLOOR(t1/10000) AS 'hour part'

-> FROM time_val;

+----------+--------------+--------------+-----------+

| t1 | t1 as number | t1 as number | hour part |

+----------+--------------+--------------+-----------+

| 15:00:00 | 150000 | 150000 | 15 |

| 05:01:30 | 50130 | 50130 | 5 |

| 12:30:20 | 123020 | 123020 | 12 |

+----------+--------------+--------------+-----------+

The same kind of conversion can be performed for date or date-and-time values. For DATETIME columns, the conversion results in a fractional part. Use FLOOR() if that is not desired:

mysql>SELECT d, d+0 FROM date_val;

+------------+----------+

| d | d+0 |

+------------+----------+

| 1864-02-28 | 18640228 |

| 1900-01-15 | 19000115 |

| 1987-03-05 | 19870305 |

| 1999-12-31 | 19991231 |

| 2000-06-04 | 20000604 |

+------------+----------+

mysql> SELECT dt, dt+0, FLOOR(dt+0) FROM datetime_val;

+---------------------+-----------------------+----------------+

| dt | dt+0 | FLOOR(dt+0) |

+---------------------+-----------------------+----------------+

| 1970-01-01 00:00:00 | 19700101000000.000000 | 19700101000000 |

| 1987-03-05 12:30:15 | 19870305123015.000000 | 19870305123015 |

| 1999-12-31 09:00:00 | 19991231090000.000000 | 19991231090000 |

| 2000-06-04 15:45:30 | 20000604154530.000000 | 20000604154530 |

+---------------------+-----------------------+----------------+

A value produced by adding zero is not the same as that produced by conversion into basic units like seconds or days. The result is essentially what you get by removing all the delimiters from the string representation of the original value. Also, conversion to numeric form works only for values that MySQL interprets temporally. If you try converting a literal string to a number by adding zero, you’ll just get the first component of the value. This is a conversion that can result in a warning:

mysql>SELECT '1999-01-01'+0, '1999-01-01 12:30:45'+0, '12:30:45'+0;

+----------------+-------------------------+--------------+

| '1999-01-01'+0 | '1999-01-01 12:30:45'+0 | '12:30:45'+0 |

+----------------+-------------------------+--------------+

| 1999 | 1999 | 12 |

+----------------+-------------------------+--------------+

1 row in set, 3 warnings (0.00 sec)

mysql> SHOW WARNINGS;

+---------+------+---------------------------------------------------------+

| Level | Code | Message |

+---------+------+---------------------------------------------------------+

| Warning | 1292 | Truncated incorrect DOUBLE value: '1999-01-01' |

| Warning | 1292 | Truncated incorrect DOUBLE value: '1999-01-01 12:30:45' |

| Warning | 1292 | Truncated incorrect DOUBLE value: '12:30:45' |

+---------+------+---------------------------------------------------------+

This same thing happens if you add zero to the result of functions such as DATE_FORMAT() and TIME_FORMAT(), or if you pull out parts of DATETIME or TIMESTAMP values with LEFT() or RIGHT(). In +0 context, the results of these functions are treated as strings, not temporal types, and conversion to a number uses only the first component of a string value.

Forcing MySQL to Treat Strings as Temporal Values

Problem

You want a string to be interpreted temporally.

Solution

Use the string in a temporal context to give MySQL a hint about how to treat it.

Discussion

If you need to make MySQL treat a string as a date or time, use it in an expression that provides a temporal context without changing the value. For example, you can’t add zero to a literal TIME-formatted string to effect a time-to-number conversion, but if you use TIME_TO_SEC() andSEC_TO_TIME(), you can. The two columns of the following result demonstrate this:

mysql>SELECT '12:30:45'+0, SEC_TO_TIME(TIME_TO_SEC('12:30:45'))+0;

+--------------+----------------------------------------+

| '12:30:45'+0 | SEC_TO_TIME(TIME_TO_SEC('12:30:45'))+0 |

+--------------+----------------------------------------+

| 12 | 123045 |

+--------------+----------------------------------------+

In the second column, the conversion to and from seconds leaves the value unchanged but results in a context where MySQL treats the result as a TIME value. For date values, the procedure is similar, but uses TO_DAYS() and FROM_DAYS():

mysql>SELECT '1999-01-01'+0, FROM_DAYS(TO_DAYS('1999-01-01'))+0;

+----------------+------------------------------------+

| '1999-01-01'+0 | FROM_DAYS(TO_DAYS('1999-01-01'))+0 |

+----------------+------------------------------------+

| 1999 | 19990101 |

+----------------+------------------------------------+

For date-and-time values, you can use DATE_ADD() to introduce a temporal context:

mysql>SELECT

-> DATE_ADD('1999-01-01 12:30:45',INTERVAL 0 DAY)+0 AS 'numeric datetime';

+------------------+

| numeric datetime |

+------------------+

| 19990101123045 |

+------------------+

Selecting Rows Based on Their Temporal Characteristics

Problem

You want to select rows based on temporal constraints.

Solution

Use a date or time condition in the WHERE clause. This may be based on direct comparison of column values with known values. Or it may be necessary to apply a function to column values to convert them to a more appropriate form for testing, such as using MONTH() to test the month part of a date.

Discussion

Most of the preceding date-based techniques were illustrated by example statements that produce date or time values as output. You can use the same techniques in WHERE clauses to place date-based restrictions on the rows selected by a statement. For example, you can select rows by looking for values that occur before or after a given date, within a date range, or that match particular month or day values.

Comparing dates to one another

The following statements find rows from the date_val table that occur either before 1900 or during the 1900s:

mysql>SELECT d FROM date_val where d < '1900-01-01';

+------------+

| d |

+------------+

| 1864-02-28 |

+------------+

mysql> SELECT d FROM date_val where d BETWEEN '1900-01-01' AND '1999-12-31';

+------------+

| d |

+------------+

| 1900-01-15 |

| 1987-03-05 |

| 1999-12-31 |

+------------+

When you don’t know the exact date you want for a WHERE clause, you can often calculate it using an expression. For example, to perform an “on this day in history” statement to search for rows in a table history to find events occurring exactly 50 years ago, do this:

SELECT * FROM history WHERE d = DATE_SUB(CURDATE(),INTERVAL 50 YEAR);

You see this kind of thing in newspapers that run columns showing what the news events were in times past. (In essence, the statement identifies those events that have reached their n-th anniversary.) If you want to retrieve events that occurred “on this day” for any year rather than “on this date” for a specific year, the statement is a bit different. In that case, you need to find rows that match the current calendar day, ignoring the year. That topic is discussed in “Comparing dates to calendar days” later in this recipe.

Calculated dates are useful for range testing as well. For example, to find dates that occur within the last seven years, use DATE_SUB() to calculate the cutoff date:

mysql>SELECT d FROM date_val WHERE d >= DATE_SUB(CURDATE(),INTERVAL 7 YEAR);

+------------+

| d |

+------------+

| 1999-12-31 |

| 2000-06-04 |

+------------+

Note that the expression in the WHERE clause isolates the date column d on one side of the comparison operator. This is usually a good idea; if the column is indexed, placing it alone on one side of a comparison enables MySQL to process the statement more efficiently. To illustrate, the preceding WHERE clause can be written in a way that’s logically equivalent but much less efficient for MySQL to execute:

... WHERE DATE_ADD(d,INTERVAL 7 YEAR) >= CURDATE();

Here, the d column is used within an expression. That means every row must be retrieved so that the expression can be evaluated and tested, which makes any index on the column useless.

Sometimes it’s not so obvious how to rewrite a comparison to isolate a date column on one side. For example, the following WHERE clause uses only part of the date column in the comparisons:

...WHERE YEAR(d) >= 1987 AND YEAR(d) <= 1991;

To rewrite the first comparison, eliminate the YEAR() call, and replace its right side with a complete date:

...WHERE d >= '1987-01-01' AND YEAR(d) <= 1991;

Rewriting the second comparison is a little trickier. You can eliminate the YEAR() call on the left side, just as with the first expression, but you can’t just add -01-01 to the year on the right side. That produces the following result, which is incorrect:

...WHERE d >= '1987-01-01' AND d <= '1991-01-01';

That fails because dates from 1991-01-02 to 1991-12-31 fail the test, but should pass. To rewrite the second comparison correctly, either of the following will do:

...WHERE d >= '1987-01-01' AND d <= '1991-12-31';

...WHERE d >= '1987-01-01' AND d < '1992-01-01';

Another use for calculated dates occurs frequently in applications that create rows that have a limited lifetime. Such applications must be able to determine which rows to delete when performing an expiration operation. You can approach this problem a couple of ways:

§ Store a date in each row indicating when it was created. (Do this by making the column a TIMESTAMP or by setting it to NOW(); see Using TIMESTAMP to Track Row Modification Times for details.) To perform an expiration operation later, determine which rows have a creation date that is too old by comparing that date to the current date. For example, the statement to expire rows that were created more than n days ago might look like this:

DELETE FROM mytbl WHERE create_date < DATE_SUB(NOW(),INTERVALn DAY);

§ Store an explicit expiration date in each row by calculating the expiration date with DATE_ADD() when the row is created. For a row that should expire in n days, you can do this:

§ INSERT INTO mytbl (expire_date,...)

VALUES(DATE_ADD(NOW(),INTERVALn DAY),...);

To perform the expiration operation in this case, compare the expiration dates to the current date to see which ones have been reached:

DELETE FROM mytbl WHERE expire_date < NOW();

Comparing times to one another

Comparisons involving times are similar to those involving dates. For example, to find times that occurred from 9 AM to 2 PM, use an expression like one of the following:

...WHERE t1 BETWEEN '09:00:00' AND '14:00:00';

...WHERE HOUR(t1) BETWEEN 9 AND 14;

For an indexed TIME column, the first method would be more efficient. The second method has the property that it works not only for TIME columns, but for DATETIME and TIMESTAMP columns as well.

Comparing dates to calendar days

To answer questions about particular days of the year, use calendar day testing. The following examples illustrate how to do this in the context of looking for birthdays:

§ Who has a birthday today? This requires matching a particular calendar day, so you extract the month and day but ignore the year when performing comparisons:

...WHERE MONTH(d) = MONTH(CURDATE()) AND DAYOFMONTH(d) = DAYOFMONTH(CURDATE());

This kind of statement commonly is applied to biographical data to find lists of actors, politicians, musicians, and so forth, who were born on a particular day of the year.

It’s tempting to use DAYOFYEAR() to solve “on this day” problems, because it results in simpler statements. But DAYOFYEAR() doesn’t work properly for leap years. The presence of February 29 throws off the values for days from March through December.

§ Who has a birthday this month? In this case, it’s necessary to check only the month:

...WHERE MONTH(d) = MONTH(CURDATE());

§ Who has a birthday next month? The trick here is that you can’t just add one to the current month to get the month number that qualifying dates must match. That gives you 13 for dates in December. To make sure that you get 1 (January), use either of the following techniques:

§ ...WHERE MONTH(d) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));

...WHERE MONTH(d) = MOD(MONTH(CURDATE()),12)+1;