Date and Time Functions - Learning MySQL and MariaDB - page 261

executed when the user identifies the bird. So, to continue this simulation, wait a bit and
then enter this SQL statement to set the time for the id_end column:
UPDATE bird_identification_tests
SET id_end = CURTIME ();
We've now updated the one row in the table by setting the value of the id_end column
to the current time. Now we can executea SELECT using the SEC_TO_TIME() function
to see how that function works:
SELECT CONCAT(name_first, SPACE(1), name_last)
AS 'Birdwatcher',
common_name AS 'Bird',
SEC_TO_TIME( TIME_TO_SEC(id_end) - TIME_TO_SEC(id_start) )
AS 'Time Elapsed'
FROM bird_identification_tests
JOIN humans USING(human_id)
JOIN rookery.birds USING(bird_id);
+-------------+------------------+--------------+
| Birdwatcher | Bird | Time Elapsed |
+-------------+------------------+--------------+
| Ricky Adams | Crested Shelduck | 00:01:21 |
+-------------+------------------+--------------+
As nice as this SQL statement is, a problem arises when the two times are in different
days, such as when the bird-watcher starts the test before midnight and finishes after mid-
night. Then the value of id_end is less than id_start , occurring seemingly before the
event started. To allow for that possibility, you have to construct a much more complex
SQL statement to includethe IF() function to test for that rare occurrence. But that
doesn't allow for when someone starts the test and waits to respond until more than 24
hours later. For that, you might want to cancel the session using other methods than those
provided by MySQL. But there may be situations in which you will be comparing times
that you will expect to be more than a day apart. For those situations, you would do better
to use the DATETIME data type along with other functions for comparing dates and times.
Those are covered in the next section.
Let's look at one more function related to adding and subtracting dates.The
PERIOD_ADD() function takes a date as the first argument and adds a specified number
of months given as the second argument. It can be used also to subtract months from a
date, if the count given for the second argument is a negative value.
PERIOD_ADD() is a bit of an oddball in this chapter because it takes a string as an argu-
ment instead of a date, and returns a string in the same format. The string consists of a