## Changing Times in MySQL

#### A Tutorial by Russell J.T. Dyer

Last month's installment in this on-going MySQL series dealt with time and date columns in MySQL and how to selectively retrieve and format time and date elements. This month's edition will go a little further by exploring special functions that are available in MySQL to modify time and date.

### The Nature of Time

For most of us, there is a morning and an afternoon in each day. Days are measured in either two twelve-hour blocks or one twenty-four-hour block. There are twelve months in a year, with each month consisting of thirty or thirty-one days. The only exception is the month of February which contains twenty-eight days usually, but once every four years it contains twenty-nine. While this all may be rather natural, putting it into a computer program can make it seem very unnatural and frustrating.

For the scenario in this article we have a MySQL database in which customers enter work requests through the web. When they enter a trouble ticket, a record is entered into a MySQL table called, `tickets`. This record contains several fields, one of which is the date that the ticket was entered called `ticket_date`. Another contains the time the ticket was entered. It's called simply, `entered`. Yet another column is called `promised`; it's the time that the customer was promised that their problem would be resolved. Both the `entered` and the `promised` columns are `time` data type columns. The value of `entered` is determined from the current time of the server. The value of `promised` is determined by adding a number of hours to the value of `entered`, depending on the urgency of the ticket set by the customer. For instance, tickets marked "ASAP" are to be completed within two hours according to our company's policy. This all works nicely in testing, but occasionally customers create tickets at odd times and on odd days.

### Around the Clock

Setting aside the potential problems for a moment, let's look at a simple example of how we might add tickets. Suppose we wanted to write a CGI script (in Perl or PHP) that will allow users to create tickets on-line any time. We might use the following SQL statement in our script:

INSERT INTO tickets (client_id, urgency, trouble, ticket_date, entered, promised) VALUES('$client_id', '$urgency', '$trouble', CURDATE(), CURTIME(), SEC_TO_TIME(TIME_TO_SEC(CURTIME()) + 7200));

If you're unfamiliar with `INSERT` statements and the use of script variables (e.g., `$client_id`), you may want to go back and read an earlier article (Creating MySQL-based Sites with PHP) in this series which explains both. For the purposes of this article, however, let's focus on the minor formula in the SQL statement above for calculating the promised time, the last line. The `TIME_TO_SEC()` function converts a time to seconds so that a calculation may be performed. In this case, the current time is converted to seconds. The formula above then adds 7200 seconds (which is two hours) to that. In order to insert the seconds sum into a `time` column (i.e., `promised`), it needs to be converted to a time format. Hence, the calculation is wrapped up in the `SEC_TO_TIME()` function.

As nice as the SQL statement above is, a problem arises when a customer runs it at 11:00 p.m (or 23:00 in MySQL time) and the promised time is to be two hours later. The SQL statement above will calculate a promised time of 25:00. What time is that in human or computer terms? As humans, we know that it's meant to be 1:00 a.m., but MySQL will need this clarified. One solution would be to place the time formula above inside of an `IF` clause in MySQL. To do this, the last line of the SQL statement would be replaced with these lines:

... IF((TIME_TO_SEC(CURTIME()) + 7200) < 86400, SEC_TO_TIME(TIME_TO_SEC(CURTIME()) + 7200), SEC_TO_TIME((TIME_TO_SEC(CURTIME()) + 7200) - 86400)));

The first element in the `IF` clause is the test. The second piece is the value used if the test passes. The third is the value if the test fails. So, if the total seconds is less than 86,400 (i.e., the number of seconds in one day), then the total seconds of the current time, converted to the time format is to be used. Otherwise, the total seconds of the current time minus 86,400 seconds, converted to the time format is to be used. Incidentally, there's an extra closing parenthesis at the end of this SQL statement excerpt because there was an opening one as part of the `VALUES` clause that's not shown here. Although the statement above works, it's a bit excessive and can be accomplished a little more succinctly if one reconsiders the purpose of the `IF` clause.

What we're trying to determine in the `IF` clause is the number of seconds into the day in which the work was promised to be done, meaning the excess amount of time of the day (i.e., one hour). For such a calculation, the modulo division operator (i.e., the `\%`) can be used. The modulo division operator will give the remainder of a division. For instance, the result of `SELECT 14 \% 5;` is 4. That is to say, 5 goes into 14 two complete times with 4 left over. As another example, the result of `SELECT 3 \% 5;` is 3; that is to say, 5 goes into 3 zero times with 3 left over. Using this arithmetic operator in the time formula above, we can eliminate the `IF` clause and use the following to accomplish our task:

... SEC_TO_TIME((TIME_TO_SEC(CURTIME()) + 7200) \% 86400));

If the current time is 23:00, then the time in seconds will be 82,800. The formula above will add 7200 to 82,800 to make 90,000 seconds. The modulo division operator will divide 86,400 into 90,000 one time, giving a remainder of 3600 seconds. The `SEC_TO_TIME` function will then convert 3600 seconds to one hour or 1:00 a.m.

### Today or Tomorrow?

There is a problem with the results from the formula at the end of the previous section. If the customer is promised 1:00 a.m., is that time today or tomorrow? Again, as humans we know that since the promised time must be after the entered time, it must be 1:00 a.m. on the following day. Since computers don't make these assumptions, though, we'll have to make some adjustments to the `tickets` table and the SQL statement. To be able to record the date and time in each column, we'll first change the column types of `entered` and `promised` from `time` to `datetime`. We'll do the following SQL statements to migrate the data and to clean up the table:

ALTER TABLE tickets, CHANGE COLUMN entered entered_old TIME, CHANGE COLUMN promised promised_old TIME, ADD COLUMN entered DATETIME, ADD COLUMN promised DATETIME; UPDATE tickets SET entered = CONCAT(ticket_date, ' ', entered_old), promised = CONCAT(ticket_date, ' ', promised_old); ALTER TABLE tickets, DROP COLUMN entered_old, DROP COLUMN promised_old, DROP COLUMN ticket_date;

The first SQL statement above alters the table to change the names of the time columns temporarily and to add the new columns with `datetime` types. If we were instead just to change the existing time columns to `datetime` types without this two step process, the data would be clobbered and reset to all zeros. The next SQL statement copies the values of the `ticket_date` column and pastes it together with the value of one of the old time columns to come up with the new date and time value for the entered and promised dates and times. The flaw in this statement, of course, is that it doesn't deal with the problems with some promised times that the previous layout caused. In fact, it reinforces it by giving a 1:00 a.m. promised time the date of the entered time. This will either have to be fixed manually if it's important to the developer, or with a script that will compare the two time columns. Either way, it's a little out of the scope of this article, so I'll move on. The last SQL statement above deletes the old time columns and the old date column now that the data has been migrated. By the way, it's a good practice to backup the data before altering a table. Also, you probably would run a `SELECT` statement before the last SQL statement above to check the migrated data before dropping the old columns.

Having changed the column types, we can now use the function `DATE_ADD`, which can deal with times that exceed twenty-four hours so that the problem with times straddling the midnight hour won't reoccur. Therefore, our on-going SQL statement becomes this:

INSERT INTO tickets (client_id, urgency, trouble, entered, promised) VALUES('$client_id', '$urgency', '$trouble', NOW(), DATE_ADD(NOW(), INTERVAL 2 HOUR));

First notice that the field `ticket_date` was eliminated and `CURTIME()` was replaced with `NOW()`, which provides the date and time in one. In the last line we see `DATE_ADD`: an interval of two hours is added to the date and time now (or rather when the record is created). If the time rolls into the next day, then the date is advanced by one and the correct hour is set accordingly.

The `DATE_ADD` function will also allow for the addition of minutes. The directive `HOUR` would be replaced with `MINUTE`. To add both hours and minutes (e.g., two hours and thirty minutes), the last line of the SQL statement above could read like this:

... DATE_ADD(NOW(), INTERVAL '2:30' HOUR_MINUTE));

If the time in which the statement is run is 11:00 p.m., the result would be 1:30 a.m. on the next day.

### Around the Calendar

The dilemma that can occur with calculations involving hours that wrap around the clock, can similarly occur with calculations involving days that roll into a new month. This problem was fairly easy to resolve with an arithmetic operator when dealing with a constant like the number of seconds in a day. However, a formula to deal with the various number of days in each month would be very lengthy. For instance, if we were simply to add five days to the date February 27, we would get February 32. Imagine trying to create an SQL statement to figure out whether that's supposed to be March 1, 2, 3, or 4--depending on whether the previous month is a regular month with 30 or 31 days, or the one irregular month with 28 or 29 days, depending on the year.

Fortunately (as you probably have already guessed), `DATE_ADD` will solve the month dilemma, as well. If instead of promising that tickets will be resolved within a couple hours of the time they are entered, we promise resolution within five days, the SQL statement would look like this:

INSERT INTO tickets (client_id, urgency, trouble, entered, promised) VALUES('$client_id', '$urgency', '$trouble', NOW(), DATE_ADD(NOW(), INTERVAL 5 DAY));

If this statement is run on February 27, then the value of `promised` would be March 3 or 4, depending on whether it is a leap year. Which one will be determined by the `DATE_ADD` function, requiring no fancy formula.

Just as hours and minutes can be mixed with `DATE_ADD`, days and hours can be mixed, as well. To make the value of `promised` two days and six hours from *now*, the last line of the SQL statement above would read like this:

... DATE_ADD(NOW(), INTERVAL '2 6' DAY_HOUR));

The function `DATE_ADD` will also allow the addition of months and of years. For instance, to increase the date by one year and two months, the SQL statement would be adjusted to look like this:

... DATE_ADD(NOW(), INTERVAL '1 2' YEAR_MONTH));

This increases the year by one and the month by two. These intervals have no effect on time or day values, though. So, if the value of `NOW()` is 2003-09-15 23:00, then the value of `promised` would become 2004-11-15 23:00, regardless of whether next year is a leap year and regardless of the number of days in each intervening month.

### Stepping Back

It stands to reason that if one wants to add days to the current date, then one will want to subtract days in an equally agreeable manner. For subtracting days we can still use the `DATE_ADD` function. Just put a negative sign in front of the interval value like this:

... DATE_ADD(NOW(), INTERVAL -5 DAY));

This will give a value five days before the current date. An alternative would be to use the `DATE_SUB` function which subtracts from the date given. The above amendment (subtracting five days from the current date) could be entered like so:

... DATE_SUB(NOW(), INTERVAL 5 DAY));

Notice that the 5 is not preceded by a negative sign. If it were, it would have the effect of adding five days.

### Conclusion

This article along with the previous one on time and date in MySQL in no way exhaust the topic. There are many more functions and tricks to manipulating temporal values in MySQL, not to mention what can be done with the extension of a script using a programming language like Perl. Plus, new functions are occasionally being added to MySQL (e.g., the `DATE_ADD` and `DATE_SUB` functions were only added in version 3.22.4 of MySQL). As you can see, though, despite it's free price, MySQL is in no way lacking as an SQL database.