Changing Times in MariaDB

The article entitled, Doing Time with MariaDB dealt with time and date columns in MariaDB and how to selectively retrieve and format time and date elements. This article will go a little further by exploring special functions that are available in MariaDB 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 MariaDB database in which customers enter work requests through the web. When they enter a trouble ticket, a record is entered into a MariaDB 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 (MariaDB Basics) 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 MariaDB 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 MariaDB will need this clarified. One solution would be to place the time formula above inside of an IF clause in MariaDB. 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 we'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 2017-09-15 23:00, then the value of promised would become 2018-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 MariaDB in no way exhaust the topic. There are many more functions and tricks to manipulating temporal values in MariaDB, not to mention what can be done with the extension of a script using a programming language like PHP. Plus, new functions are occasionally being added to MariaDB.