Learning MySQL and MariaDB (2015)

Part IV. Built-In Functions

Chapter 11. Date and Time Functions

For many of us, there is a morning and an afternoon in each day. Days are measured in either two 12-hour blocks or one 24-hour block. There are 12 months in a year, with each month consisting of 30 or 31 days, except for one month which usually contains 28 days, but once every four years it contains 29. While this all may be rather natural or at least familiar to humans, putting it in terms a computer can manipulate can make it seem very unnatural and frustrating. However, the recording and manipulating of date and time in a database is a very common requirement.

For storing dates and times, known as temporal data, one needs to know which type of column to use in a table. More important is knowing how to record chronological data and how to retrieve it in various formats. Although this seems to be basic, there are many built-in time functions that can be used for more accurate SQL statements and better formatting of data. In this chapter, we will explore these various aspects of date and time functions in MySQL and MariaDB.

Date and Time Data Types

Because dates and times are ultimately just strings containing numbers, they could be stored in a regular character column. However, there are data types designed specifically for dates and times. By using temporal data type columns, you can make use of several built-in functions offered by MySQL and MariaDB. So before we start learning about the date and time functions, let’s look at the data types that are available for recording date and time.

There are five temporal data types in MySQL and MariaDB: DATE for storing dates, TIME for storing time, DATETIME and TIMESTAMP for both date and time, and YEAR for a year:

DATE

This records the date only, in the format yyyy-mm-dd. You may prefer a different format (e.g., 02-14-2014 for St. Valentine’s Day), but you can’t change how the date is stored — at least not without changing the source code of MySQL. But other functions discussed in this chapter let you display the date in the format you like.

This data type has a limit to the range of dates it will accept. It allows dates from as early as 1000-01-01 to as late as 9999-12-31. That’s far into the future, but you wouldn’t use this for recording historical dates in the first millennium.

TIME

This records time in the format hhh:mm:ss. It accepts times ranging from -838:59:59 to 838:59:59. If you give it a time outside of that range or in some way not valid, it records the time as all zeros.

You may be wondering how you could have a time in which you need three digits for the hour. This is so that you can record how much time has elapsed for an event or when comparing two times, rather than just recording the time of day. For instance, you might want to note that something took 120 hours to complete. You could do this with two columns, one for recording the start time and the other the end time, and then compare them as needed. But this data type allows you to record the difference in one column, rather than recalculate each time you want that result.

DATETIME

This records a combination of date and time in the format yyyy-mm-dd hh:mm:ss. It accepts dates and times from 1000-01-01 00:00:00 to 9999-12-31 23:59:59. That’s the same range as DATE, but with the addition of the full range of a 24-hour day. As of version 5.6 of MySQL, fractions of a second are possible.

TIMESTAMP

This is similar to DATETIME, but more limited in its range of allowable time. Despite the name, it’s not limited to time, but covers a range of dates from 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC. It’s meant for relatively current dates and corresponds to the “epoch” chosen by the designers of the Unix operating system. As of version 5.6 of MySQL, fractions of a second are possible.

Although you can set the value of a column manually using this data type, whenever you insert a row or update a row without specifying an explicit value, MySQL automatically updates the column’s value to the current date and time. That can be very convenient for some applications such as logging, but can cause you problems if you’re unaware of it or don’t allow for it. This is only for the first column in a table which uses TIMESTAMP. For subsequent TIMESTAMP columns, you would have to specify a couple of options to have the same effect: ON UPDATE CURRENT_TIMESTAMP and ON INSERT CURRENT_TIMESTAMP.

YEAR

This records just a year in a column, in the format yyyy. It could be set to two digits (by defining the column as YEAR(2) with an explicit number), but that’s deprecated and causes problems. So don’t record years in two-digit formats with this data type. This data type is also meant for birth years; it allows years from 1901 to 2155. If you give it an invalid value or a year outside of the allowed range, it records the year as 0000.

NOTE

Given some of the limitations of these data types, you may need to use a nontemporal data type for dates outside of the allowed ranges. You could use the INT data type to store each component of a date, or CHAR data type to store dates in a fixed width. For instance, you might have one INT column for storing the month, another for the day, and one CHAR(4) column to store years before the 20th century.

That can work generally, but it can be a problem when you try to do a calculation with these data types. Suppose you want to store February 15 in two INT columns: 2 in my_month and 15 in my_day. If you were to add 20 days to the value of my_day, you would get an invalid date of February 35. To deal with this, you would have to construct a complex SQL statement to adjust the my_day and the my_month columns. Plus, you’d have to update the column you create for the year value when a date change pushes the values into a different year. You’d have similar problems if you tried to use INT to store times. All of this complexity is eliminated by using temporal data types for columns, so that you can use date functions provided with MySQL and MariaDB. These types have built-into complex calculations so that you don’t have to worry about that.

Now that you’re familiar with the temporal data types in MySQL and MariaDB (and hopefully, appreciate them), let’s look at some examples of how you might use them with date and time functions. For some of the examples in this chapter, we’ll use the tables we’ve already created, which have columns with these data types.

Current Date and Time

The most basic date and time functions are those related to the current date and time. They may be used for recording the current date and time in a column, for modifying results based on the current date and time, or for displaying the date and time in a results set. Let’s start with the simplest one, NOW(), which determines what time it is when you execute the statement. Enter the first line shown here in mysql (an example of the results follow):

SELECT NOW( );

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

| NOW( ) |

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

| 2014-02-08 09:43:09 |

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

As you can see, that returns the date and time on a server in a format that matches the format of the DATETIME data type So if you have a column in a table that uses that data type, you can use the NOW() function to conveniently insert the current date and time into the column. Thebird_sightings table has a column that uses the DATETIME data type, the time_seen column. Here’s an example of how we might enter a row into that table using NOW():

INSERT INTO bird_sightings

(bird_id, human_id, time_seen, location_gps)

VALUES (104, 34, NOW( ), '47.318875; 8.580119');

This function can also be used with an application, or with a script for a web interface so that the user can record bird sightings without having to enter the time information.

NOTE

There are a few synonyms for the NOW() function: CURRENT_TIMESTAMP(), LOCALTIME(), and LOCALTIMESTAMP(). They return the exact same results. Synonyms such as these are provided so that MySQL and MariaDB will conform to functions in other SQL database systems. This way, if you have an application that uses another database (e.g., PostgreSQL, Sybase, Oracle), you can more easily replace it with MySQL without having to change the code in your applications.

The NOW() function returns the date and time at the start of the SQL statement containing it. For most purposes, this is fine: the difference between the time at the start and at the completion of an SQL statement is usually minimal and irrelevant. But you may have a situation in which an SQL statement takes a long time to execute, and you want to record the time at a certain point in that process. The SYSDATE() function records the time at which the function is executed, not the end of the statement. To see the difference, we can introduce the SLEEP() function to tell MySQL to pause execution for a given number of seconds. Here’s a simple example showing the difference between NOW() and SYSDATE():

SELECT NOW(), SLEEP(4) AS 'Zzz', SYSDATE(), SLEEP(2) AS 'Zzz', SYSDATE();

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

| NOW() | Zzz | SYSDATE() | Zzz | SYSDATE() |

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

| 2014-02-21 05:44:57 | 0 | 2014-02-21 05:45:01 | 0 | 2014-02-21 05:45:03 |

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

1 row in set (6.14 sec)

Notice that the difference between the time returned for NOW() and for the first SYSDATE() is four seconds, the amount given with the first execution of SLEEP(). The time between the two executions of SYSDATE() is two seconds, the amount given with SLEEP() the second time. Notice also that the message after the results shows it took a tad more than six seconds to execute this SQL statement. You probably won’t use SYSDATE() often — maybe never. It’s useful primarily when you execute very complex SQL statements or for more advanced usage (e.g., within stored procedures and triggers). Let’s move on to more common usage of functions related to the current date and time.

If the data type for a column is not DATETIME, you can still use the NOW() to get and store the values you need. For instance, if the time_seen column had a data type of DATE and you entered the preceding INSERT statement, you’d get a warning saying data truncated for column. However, it would still store the date correctly. A similar effect would occur on a TIME column: you’d get a warning, but the time would be recorded correctly. It’s better, though, to use the correct function. For DATE columns, use CURDATE(). For TIME columns, use CURTIME(). The following example compares these temporal functions:

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

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

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

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

| 2014-02-08 10:23:32 | 2014-02-08 | 10:23:32 |

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

All three of these functions and their synonyms use formats readable or easily understandable by humans. There are, however, built-in functions that return the Unix time, which is the number of seconds since the “epoch” mentioned earlier. These can be useful when comparing two temporal values. The following example shows the equivalent of NOW() as a TIMESTAMP:

SELECT UNIX_TIMESTAMP( ), NOW( );

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

| UNIX_TIMESTAMP( ) | NOW( ) |

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

| 1391874612 | 2014-02-08 10:50:12 |

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

This returns the number of seconds since since January 1, 1970. Let’s test that. Here’s a simple calculation to determine the number of years since the start of 1970, and a more complicated way of determining it:

SELECT (2014 - 1970) AS 'Simple',

UNIX_TIMESTAMP( ) AS 'Seconds since Epoch',

ROUND(UNIX_TIMESTAMP( ) / 60 / 60 / 24 / 365.25) AS 'Complicated';

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

| Simple | Seconds since Epoch | Complicated |

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

| 44 | 1391875289 | 44 |

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

This was run near the start of the year 2014 so we used the ROUND() function to round down the number of years for a simple comparison. It’s good to do exercises like this to confirm and to better know functions like this one. It helps you to understand and trust them.

Let’s look at a more meaningful example in which you might want to use Unix time. Suppose you want to know how many days ago our bird-watchers spotted a particular bird, a Black Guineafowl (bird_id 309). To do this, we can use a join like so:

SELECT CONCAT(name_first, SPACE(1), name_last) AS 'Birdwatcher',

ROUND((UNIX_TIMESTAMP( ) - UNIX_TIMESTAMP(time_seen)) / 60 / 60 / 24)

AS 'Days Since Spotted'

FROM bird_sightings JOIN humans USING(human_id)

WHERE bird_id = 309;

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

| Birdwatcher | Days Since Spotted |

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

| Marie Dyer | 129 |

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

In this example, we used CONCAT() to put together the bird-watcher’s first and last name. We issued the first UNIX_TIMESTAMP() with no argument, so it used the current date and time. The second UNIX_TIMESTAMP() specifies a column (time_seen) containing the date our bird-watchers spotted each bird. The function changed the value to a Unix timestamp so that we could do a comparison

There are other ways and other functions that may be used to compare dates and times. We’ll look at those later in this chapter. Let’s look next at how to extract the date and time components.

Extracting Date and Time Components

Temporal data types store more information than you may sometimes want. There will be situations in which you don’t want a full date or a time to the second. Because of this, there are functions that will extract any component of a temporal value you may want, as well as some common permutations. Let’s look first at some basic functions for extracting just the date and just the time, then we’ll look at ones for each component.

A DATETIME column, as the name implies, contains both the date and the time. If you want to extract just the date from such a value, you can use the DATE() function. To extract just the time, use TIME(). Let’s look at an example of these two. We’ll again select the time_seen value for sightings of a Black Guineafowl:

SELECT CONCAT(name_first, SPACE(1), name_last) AS 'Birdwatcher',

time_seen, DATE(time_seen), TIME(time_seen)

FROM bird_sightings

JOIN humans USING(human_id)

WHERE bird_id = 309;

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

| Birdwatcher | time_seen | DATE(time_seen) | TIME(time_seen) |

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

| Marie Dyer | 2013-10-02 07:39:44 | 2013-10-02 | 07:39:44 |

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

That was easy: DATE() returned just the date from time_seen and TIME() just the time. However, you may want to extract just one component of a date or time. You can do this with all of the temporal data types, as long as the column contains the component you want — you can’t get the hour from a YEAR column.

To extract only the hour of a time saved in a column, the HOUR() function could be used. For the minute and second, there’s MINUTE() and SECOND(). These may be used with DATETIME, TIME, and TIMESTAMP columns. Let’s see how the results from them might look. Enter the following in mysql:

SELECT CONCAT(name_first, SPACE(1), name_last) AS 'Birdwatcher',

time_seen, HOUR(time_seen), MINUTE(time_seen), SECOND(time_seen)

FROM bird_sightings JOIN humans USING(human_id)

WHERE bird_id = 309 \G

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

Birdwatcher: Marie Dyer

time_seen: 2013-10-02 07:39:44

HOUR(time_seen): 7

MINUTE(time_seen): 39

SECOND(time_seen): 44

These functions will allow you to use, assess, and compare each component of the time for a column. You can break apart a date, as well.

To extract the year, month, and day, you could use the YEAR(), MONTH(), and DAY() functions. You have to give a date value as the argument for each function. This can be a column that contains a date, or a string value that contains a date (e.g., ‘2014-02-14’, including the quotes). It cannot be a number, unless the number is properly ordered. For instance, the numeric value 20140214 is acceptable, but not 2014-02-14 (without quotes) or 2014 02 14 (with spaces). Here’s the same SQL statement as before, but using these functions instead:

SELECT CONCAT(name_first, SPACE(1), name_last) AS 'Birdwatcher',

time_seen, YEAR(time_seen), MONTH(time_seen), DAY(time_seen),

MONTHNAME(time_seen), DAYNAME(time_seen)

FROM bird_sightings JOIN humans USING(human_id)

WHERE bird_id = 309 \G

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

Birdwatcher: Marie Dyer

time_seen: 2013-10-02 07:39:44

YEAR(time_seen): 2013

MONTH(time_seen): 10

DAY(time_seen): 2

MONTHNAME(time_seen): October

DAYNAME(time_seen): Wednesday

This example has a couple of other date functions: MONTHNAME() to get the name of the month for the date; and DAYNAME() to get the name of the day of the week for the date. Using all of these functions, you can put together nicer looking results or easily check date information. Let’s look at how you might use the date and time functions to re-order date results. Here’s an example that retrieves a list of endangered birds spotted by the members of the site:

SELECT common_name AS 'Endangered Bird',

CONCAT(name_first, SPACE(1), name_last) AS 'Birdwatcher',

CONCAT(DAYNAME(time_seen), ', ', MONTHNAME(time_seen), SPACE(1),

DAY(time_seen), ', ', YEAR(time_seen)) AS 'Date Spotted',

CONCAT(HOUR(time_seen), ':', MINUTE(time_seen),

IF(HOUR(time_seen) < 12, ' a.m.', ' p.m.')) AS 'Time Spotted'

FROM bird_sightings

JOIN humans USING(human_id)

JOIN rookery.birds USING(bird_id)

JOIN rookery.conservation_status USING(conservation_status_id)

WHERE conservation_category = 'Threatened' LIMIT 3;

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

| Endangered Bird | Birdwatcher | Date Spotted | Time |

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

| Eskimo Curlew | Elena Bokova | Tuesday, October 1, 2013 | 5:9 a.m. |

| Red-billed Curassow | Marie Dyer | Wednesday, October 2, 2013 | 7:39 a.m. |

| Red-billed Curassow | Elena Bokova | Wednesday, October 2, 2013 | 8:41 a.m. |

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

This is a very cluttered SQL statement. Yes, because it involves using JOIN a few times, it’s lengthy as one would expect. But using CONCAT() twice with so many date and time functions clutters it unnecessarily. Notice that 5:9 is displayed for the hours and minutes, instead of 5:09. That’s because the function, MINUTE() doesn’t pad with zeroes. We could fix that by using the LPAD() function, but that would be more clutter. We complicated the statement even further by using the IF() function to label the time morning or evening (i.e., a.m. or p.m.).

There’s a cleaner, easier way to reformat dates and times using date and time formatting functions, which are described in the next section. Meanwhile, you can reduce the number of date and extraction functions to a single one: EXTRACT().

The EXTRACT() function can be used to extract any component of a date or time. The syntax is simple and a little verbose: EXTRACT(interval FROM date_time). The intervals given are similar to the names of the date and time extraction functions we’ve already reviewed: MONTH for month, HOURfor hour, and so on. There are also some combined ones such as YEAR_MONTH and HOUR_MINUTE. For a list of intervals allowed with EXTRACT() and similar date and time functions, see Table 11-1.

Table 11-1. Date and time intervals and formats

Let’s look at a simple example of this function by redoing the example that queried for the bird-watchers who saw the Black Guineafowl. Here it is again with EXTRACT():

SELECT time_seen,

EXTRACT(YEAR_MONTH FROM time_seen) AS 'Year & Month',

EXTRACT(MONTH FROM time_seen) AS 'Month Only',

EXTRACT(HOUR_MINUTE FROM time_seen) AS 'Hour & Minute',

EXTRACT(HOUR FROM time_seen) AS 'Hour Only'

FROM bird_sightings JOIN humans USING(human_id)

LIMIT 3;

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

| time_seen | Year & Month | Month Only | Hour & Minute | Hour Only |

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

| 2013-10-01 04:57:12 | 201310 | 10 | 457 | 4 |

| 2013-10-01 05:09:27 | 201310 | 10 | 509 | 5 |

| 2013-10-01 05:13:25 | 201310 | 10 | 513 | 5 |

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

As you can see, when you use EXTRACT() with single intervals, it works fine as a consistent substitute for the other temporal extraction functions. Asking for HOUR_MINUTE doesn’t produce very nice results, because there is no colon between the hour and minute (for instance, 4:57 is shown as 457). When you use EXTRACT() with combined intervals, it returns results combined together with no formatting. That may be what you want sometimes, but other times you might want to format a date or time. Once again, you’ll need the date and time formatting functions in the next section.

Formatting Dates and Time

In the first section of this chapter, we looked briefly at the temporal data types in MySQL and MariaDB, including the formats in which dates and times are stored. I mentioned that if you don’t like those formats, there are built-in functions that may be used to return temporal data in different formats. The most useful is the DATE_FORMAT() function, and a similar one, TIME_FORMAT(). You can use these to format date and time values taken from a column, a string, or another function. With these two functions, you can specify the format you want with simple formatting codes. Let’s redo the SQL statement from the example at the end of the previous section, using these functions:

SELECT common_name AS 'Endangered Bird',

CONCAT(name_first, SPACE(1), name_last) AS 'Birdwatcher',

DATE_FORMAT(time_seen, '%W, %M %e, %Y') AS 'Date Spotted',

TIME_FORMAT(time_seen, '%l:%i %p') AS 'Time Spotted'

FROM bird_sightings

JOIN humans USING(human_id)

JOIN rookery.birds USING(bird_id)

JOIN rookery.conservation_status USING(conservation_status_id)

WHERE conservation_category = 'Threatened' LIMIT 3;

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

| Endangered Bird | Birdwatcher | Date Spotted | Time |

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

| Eskimo Curlew | Elena Bokova | Tuesday, October 1, 2013 | 5:09 AM |

| Red-billed Curassow | Marie Dyer | Wednesday, October 2, 2013 | 7:39 AM |

| Red-billed Curassow | Elena Bokova | Wednesday, October 2, 2013 | 8:41 AM |

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

This is still a hefty SQL statement, but the portions related to formatting the date and time is more straightforward. With the DATE_FORMAT() and the TIME_FORMAT() functions, you give the column to format as the first argument and then provide a string in quotes that contains formatting codes and text to lay out how you want the date and time formatted. Incidentally, the DATE_FORMAT() function will return times in addition to dates. So there’s really no need to use TIME_FORMAT(). It’s just a matter of style.

The problems we had in the previous two examples (i.e., lack of padding for minutes, no colon, and the need for IF() to indicate morning or evening), doesn’t exist here. We took care of all of that by using the '%l:%i %p' formatting codes. If we were willing to include the seconds, we could replace those three formatting codes with just '%r'. Table 11-2 shows a list of formatting codes and what they return.

Table 11-2. Date and time formatting codes

Different places in the world prefer various standards for formatting the date and time. In the next section, we’ll look at this and how to adjust to the time zones of other regions.

Adjusting to Standards and Time Zones

There a few standards for formatting the date and time. For instance, the last day of December and the year could be written numerically as 12-31-2014 or 31-12-2014. Which standard you will use on a server may be based on where you’re located in the world, or your employer and client preferences, or some other factor. To get the date format for a particular standard, you can use GET_FORMAT(). Enter the following to try this:

SELECT GET_FORMAT(DATE, 'USA');

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

| GET_FORMAT(DATE, 'USA') |

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

| %m.%d.%Y |

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

As the name implies, GET_FORMAT() checks for a particular place or locale and returns the string that can be used in DATE_FORMAT() to produce the desired format. It might be a bit surprising that the U.S. format uses periods instead of hyphens to separate elements of the date. In GET_FORMAT, the first argument indicates whether you want the date, the time, or both (i.e., DATE, TIME, or DATETIME). The second argument specifies the date or time standard, and can be one of the following:

§ EUR for Europe

§ INTERNAL for the format in which time is stored, without punctuation

§ ISO for ISO 9075 standard

§ JIS for Japanese Industrial Standard

§ USA for United States

The ISO standard (yyyy-mm-dd hh:mm:ss) is the default for displaying the date and time in MySQL.

Enter this simple example that uses GET_FORMAT():

SELECT GET_FORMAT(DATE, 'USA'), GET_FORMAT(TIME, 'USA');

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

| GET_FORMAT(DATE, 'USA') | GET_FORMAT(TIME, 'USA') |

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

| %m.%d.%Y | %h:%i:%s %p |

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

Try running GET_FORMAT for various standards in order to become familiar with the different layouts — or check the documentation. After you’ve done that, execute the following SQL statement to see how this function works in conjunction with DATE_FORMAT():

SELECT DATE_FORMAT(CURDATE(), GET_FORMAT(DATE,'EUR'))

AS 'Date in Europe',

DATE_FORMAT(CURDATE(), GET_FORMAT(DATE,'USA'))

AS 'Date in U.S.',

REPLACE(DATE_FORMAT(CURDATE(), GET_FORMAT(DATE,'USA')), '.', '-')

AS 'Another Date in U.S.';

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

| Date in Europe | Date in U.S. | Another Date in U.S. |

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

| 18.02.2014 | 02.18.2014 | 02-18-2014 |

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

Because I don’t agree that U.S. dates should use periods, the last field shows how to use the REPLACE() function to replace the periods with dashes. GET_FORMAT() isn’t a function you’ll use often, but it’s good to know about it. A more useful and somewhat similar function is CONVERT_TZ().

CONVERT_TZ() converts a time to a given time zone. Before we can convert to a given time zone, though, we need to know which time zone our server is using. We can determine this by entering the following from the mysql client:

SHOW VARIABLES LIKE 'time_zone';

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

| Variable_name | Value |

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

| time_zone | SYSTEM |

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

This shows that my server is using the filesystem time, which is probably the same time zone where it’s located. Suppose the server we use for our bird-watching site is located in Boston, Massachusetts, which is in the U.S. Eastern Time Zone. If a member enters information in the morning about a bird sighting in Rome, Italy, which is in the Central European Time Zone, we don’t want them to see the time in Boston after they save the entry. We would want the time adjusted for the time zone in which the bird was sighted. Otherwise people in the United States might think that Italians often see birds during the night and nocturnal birds such as owls during the day. So we’ll use CONVERT_TZ() to adjust the times appropriately.

The syntax for CONVERT_TZ() requires three arguments: the date and time to convert, the time zone from whence the time came, and the time zone to which to convert. Let’s look at an example:

SELECT common_name AS 'Bird',

CONCAT(name_first, SPACE(1), name_last) AS 'Birdwatcher',

DATE_FORMAT(time_seen, '%r') AS 'System Time Spotted',

DATE_FORMAT(CONVERT_TZ(time_seen, 'US/Eastern', 'Europe/Rome'), '%r')

AS 'Birder Time Spotted'

FROM bird_sightings

JOIN humans USING(human_id)

JOIN rookery.birds USING(bird_id)

JOIN rookery.conservation_status USING(conservation_status_id) LIMIT 3;

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

| Bird | Birdwatcher |System Time Spotted| Birder Time Spotted |

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

| Whimbrel | Richard Stringer | 04:57:12 AM | 10:57:12 AM |

| Eskimo Curlew | Elena Bokova | 05:09:27 AM | 11:09:27 AM |

| Marbled Godwit | Rusty Osborne | 05:13:25 AM | 11:13:25 AM |

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

Notice that the time zones on the system are six hours earlier than the converted times. Of course, this is assuming that everyone is located in the same time zone as Rome. What we could do is add a column to the humans table to include the time zone in which the user is located or prefers. When a user registers, we can guess at their time zone based on what their web browser tells us or some other clever method. But then we could give the user an option of choosing another time zone in case we guessed wrong. However you determine and store the time zone, you would modify the preceding SQL statement to change the time to which CONVERT_TZ() converts to that value.

Notice that the time zones we’re giving for CONVERT_TZ() are not limited to three-character code (e.g., CET for Central European time). They’re based on the time zone names in MySQL, which include CET. If you ran the preceding SQL statement and it returned null values for the field containing CONVERT_TZ(), it may be because the time zone information hasn’t been loaded. When MySQL or MariaDB are installed, on Unix-type systems you will find the time zone files in the /usr/share/zoneinfo directory. If you get a listing of that directory, you’ll see the names that may be used for the time zone arguments in CONVERT_TZ(). For instance, you will see a directory named US. Within it will be a file named Eastern. It’s from these two pieces of information that we get the value US/Eastern. To install the time zone file, enter the following, changing the file path to wherever the time zone files are located:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -p -u root mysql

If your server runs on Windows, you may have to go to Oracle’s site to download time zone tables). That web page will provide some instructions on installing the package you download. After you’ve installed the time zone files, try the previous SQL statement again to be sure everything was installed properly.

Rather than use the time zone where our web server happens to be located, we could use some other time zone. We could change the time zone for the server, without having to relocate it or change the filesystem clock. We could set the server to a more global time zone such as Greenwich Mean Time (GMT or UTC). Because birdwatching has some roots in England thanks to botanists like Joseph Banks and Charles Darwin, let’s use GMT. To set the time zone, we can use the SET statement like so:

SET GLOBAL time_zone = 'GMT';

If we wanted to set only the time zone for the current session, we wouldn’t include the GLOBAL flag. It would be better to set this value globally in the server’s configuration file (i.e., my.cnf or my.ini) so it isn’t reset when the server is rebooted. To do that, add this line to the [mysqld] section:

default-time-zone='GMT'

If you use that method, instead of using SET, you’ll have to restart the server for it to take effect. Once you’ve done that, run the SHOW VARIABLES statement again to see the results.

Setting the time zone on a server, knowing the user’s time zone, and adjusting times using CONVERT_TZ() helps the user to feel he is part of the community of a website. Otherwise, the times shown will make the user feel like he is an outsider. So learn to use CONVERT_TZ() so that your sites and services will be part of the global community.

Adding and Subtracting Dates and Time

MySQL and MariaDB include several built-in functions that may be used to change a given date or time. You can use them to change a date to a future one by adding time, or change a date to a past one by subtracting time. The main functions that do this, or perhaps the most popular ones, areDATE_ADD() and DATE_SUB(). The syntax for both of these is the same: the first argument is the date to be modified and the second argument is the amount of time. The amount of time is presented with the keyword INTERVAL, followed by a count of intervals, followed by the date or time factor (e.g., INTERVAL 1 DAY).

Let’s look at an example using DATE_ADD(). Suppose we want to extend the membership of all of our members who live in the United Kingdom by three months. To do this, we would enter the following:

UPDATE humans

SET membership_expiration = DATE_ADD(membership_expiration, INTERVAL 3 MONTH)

WHERE country_id = 'uk'

AND membership_expiration > CURDATE( );

In this example, we’re adding three months to the current membership_expiration, but just for members who are in the U.K., but not for those whose membership has already expired. Notice that we’re using a simpler operator, in this case the greater-than sign (>), to compare two day values in the WHERE clause. Notice also how we had to set the membership_expiration column equal to the modified value of itself. Date and time functions don’t change the value of columns simply by being executed. You have to use them in conjunction with other methods for them to affect stored data. For a list of intervals allowed with DATE_ADD() and similar date and time functions, see Table 11-1.

Let’s look at another example using DATE_SUB(). Suppose a member named Melissa Lee renewed her membership for two years, but meant to renew it for only one year. You could enter the following SQL statement to make that adjustment:

UPDATE humans

SET membership_expiration = DATE_SUB(membership_expiration, INTERVAL 1 YEAR)

WHERE CONCAT(name_first, SPACE(1), name_last) = 'Melissa Lee';

Because there may be more than one Melissa Lee in our database, we should have first determined her human_id and used that in the WHERE clause.

DATE_ADD() is a very useful function so let’s look at some more examples using it. First, let’s redo the previous example to use DATE_ADD() instead of DATE_SUB(). You would enter it like this:

UPDATE humans

SET membership_expiration = DATE_ADD(membership_expiration, INTERVAL -1 YEAR)

WHERE CONCAT(name_first, SPACE(1), name_last) = 'Melissa Lee';

This is exactly the same as the previous example, except that we’re using DATE_ADD() and we changed the count of the interval to a negative number to indicate that one year should be subtracted and not added, despite the name of the function.

Let’s look at another example with DATE_ADD(). Suppose one of the members of our site recorded a bird sighting in the bird_sightings table, but for some reason the day and time is off. She lets us know that the entry in time_seen should be set to one day and two hours later. After we have determined the sighting_id, we can execute this SQL statement to update the date and time:

UPDATE bird_sightings

SET time_seen = DATE_ADD(time_seen, INTERVAL '1 2' DAY_HOUR)

WHERE sighting_id = 16;

In this example, the argument for the interval count is a combination of two intervals, DAY_HOUR for both DAY and HOUR. We list the counts in the same order, and put them within quotes. If we want to subtract the intervals (i.e., one day and two hours earlier), we would put a negative sign within the quotes before one of the values. Incidentally, you can’t do a combination of subtracting and adding within the same DATE_ADD(). You’d have to do either two passes at the column, or embed one call within the other. Table 11-1 lists other acceptable combined intervals.

When we use DATE_ADD() and similar functions to have MySQL calculate a new date or time, it goes through a process behind the scenes to determine the new result that is requested. Basically, it counts the number of seconds between dates and times, and then returns the new date and time. There may be situations in which you want to determine the method of those calculations, when you want more control over those calculations. For those situations, there are the TIME_TO_SEC() and SEC_TO_TIME() functions.

The TIME_TO_SEC() function converts a time to seconds so that a calculation may be performed easily. If you give it a date and time value, it uses only the time portion. Let’s look at a very simple example of this to see what the results from it mean:

SELECT TIME(NOW()),

TIME_TO_SEC(NOW()),

TIME_TO_SEC(NOW()) / 60 /60 AS 'Hours';

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

| NOW() | TIME_TO_SEC(NOW()) | Hours |

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

| 2014-02-18 03:30:00 | 12600 | 3.50000000 |

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

For the first field here, we’re getting the current time. Notice that the time portion is exactly 3:30 a.m. For the second field, we’re using TIME_TO_SEC() to get the number of seconds for that time: three and a half hours into the day. The third field is a calculation to confirm that: 12,600 seconds equals 3.5 hours.

Conversely, if you know the number of seconds that have elapsed since the start of an event — whether it be the start of a day or an action — you can use the SEC_TO_TIME() function to give you a time. Suppose you have two events and you want to know how much time elapsed between them. For instance, we might have a bird identification test online. The user would be presented with an image of a bird and asked to identify it. We would record the time when the image is displayed. When the user enters the correct identification, that time is recorded in another column in the same table. We could use SEC_TO_TIME() to get the difference between the two times, but in a time format (i.e., hh:mm:ss). Let’s create an example of that by first creating a table to record each bird-watcher’s test results:

CREATE TABLE bird_identification_tests

(test_id INT AUTO_INCREMENT KEY,

human_id INT, bird_id INT,

id_start TIME,

id_end TIME);

There’s not much to this table: we just want to record the human_id for the member, the bird_id for the image presented to the member, and then the start and completion times. We don’t care about the date, just how long it took the member to identify the bird. Let’s insert some data into that table, just one row of data so that we’ll be able to try the SEC_TO_TIME() function:

INSERT INTO bird_identification_tests

VALUES(NULL, 16, 125, CURTIME(), NULL);

Notice that we didn’t provide a value for the id_end column. That will be set when the member completes the identification. We’re simulating this scenario, but if we were doing this for a site, we would embed this INSERT statement in a script that’s executed when the user is shown a bird image. Another script containing an UPDATE statement would be 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 execute a 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 midnight. 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 include the 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 argument instead of a date, and returns a string in the same format. The string consists of a year as either two or four digits, followed by a month as two digits (e.g., April 2014 could be either 1404 or 201404). Let’s try out this function with the birdwatchers database.

Suppose we want a count of bird sightings recorded by each member, but just for the previous quarter. This seems like it would be simple to do, just by using QUARTER() in the WHERE clause of a SELECT statement. Such an SQL statement might look like this:

SELECT CONCAT(name_first, SPACE(1), name_last) AS 'Birdwatcher',

COUNT(time_seen) AS 'Sightings Recorded'

FROM bird_sightings

JOIN humans USING(human_id)

WHERE QUARTER(time_seen) = (QUARTER(CURDATE()) - 1)

AND YEAR(time_seen) = (YEAR(CURDATE( )) - 1)

GROUP BY human_id LIMIT 5;

Empty set (0.14 sec)

An empty set was returned. This is because the result of QUARTER(CURDATE()) is 1, because I happened to execute this example during the first quarter of the year. So, QUARTER(CURDATE()) - 1 equals 0. Because all of the rows will have a date in quarters 1 through 4 (i.e., QUARTER(time_seen)), none will match. If I entered this statement during a different quarter, it would return results for the wrong quarter (the previous one).

Therefore, we have to adjust this SQL statement. We can do this by using PERIOD_ADD() a couple of times, along with a few other date functions we covered earlier. Here’s how we could get the list of people and the number of sightings they recorded for last quarter, regardless of the quarter in which it’s executed:

SELECT CONCAT(name_first, SPACE(1), name_last) AS 'Birdwatcher',

COUNT(time_seen) AS 'Sightings Recorded'

FROM bird_sightings

JOIN humans USING(human_id)

WHERE CONCAT(QUARTER(time_seen), YEAR(time_seen)) =

CONCAT(

QUARTER(

STR_TO_DATE(

PERIOD_ADD( EXTRACT(YEAR_MONTH FROM CURDATE()), -3),

'%Y%m') ),

YEAR(

STR_TO_DATE(

PERIOD_ADD( EXTRACT(YEAR_MONTH FROM CURDATE()), -3),

'%Y%m') ) )

GROUP BY human_id LIMIT 5;

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

| Birdwatcher | Sightings Recorded |

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

| Richard Stringer | 1 |

| Rusty Osborne | 1 |

| Elena Bokova | 3 |

| Katerina Smirnova | 3 |

| Anahit Vanetsyan | 1 |

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

I indented this SQL statement plenty to make it easier to read. We’re using EXTRACT() to extract the year and month from the CURDATE() and to put it in the format we need for PERIOD_ADD() (i.e., yyyymm). The first time we use PERIOD_ADD(), it’s getting the number of the previous quarter. The second time we use this function, it’s getting the year of that previous quarter. We use STR_TO_DATE to convert the result of PERIOD_ADD to a date.

Then we’re using CONCAT() to put the quarter and year together. We’ll compare that to the quarter and year we’ll concatenate from time_seen. This process would be simpler if EXTRACT() had an option of YEAR_QUARTER. Then we wouldn’t need to determine the date of the previous quarter twice, extract the year and month separately, and concatenate them. Sometimes we push the limits of MySQL and MariaDB. But they occasionally add new features and options. For now, there are ways to accomplish what you want with more complex SQL statements.

Comparing Dates and Times

We’ve seen, in a few examples in this book, some ways to compare values containing dates and times. Several functions are designed specifically for this task. The most straightforward ones are DATEDIFF() and TIMEDIFF(). With these, you can easily compare two dates or times. Let’s look at some examples of how you might use them.

The humans table contains a column holding the date in which a person’s membership expires, membership_expiration. Suppose that we want to display the number of days until their membership expires on the member’s profile page, to remind them. For that requirement, we can use theDATEDIFF() function in an SQL statement similar to the following:

SELECT CURDATE() AS 'Today',

DATE_FORMAT(membership_expiration, '%M %e, %Y')

AS 'Date Membership Expires',

DATEDIFF(membership_expiration, CURDATE())

AS 'Days Until Expiration'

FROM humans

WHERE human_id = 4;

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

| Today | Date Membership Expires | Days Until Expiration |

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

| 2014-02-13 | September 22, 2013 | -144 |

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

Notice that the result here from DATEDIFF() is a negative amount. That’s because the date contained in membership_expiration is a date before the current date, the date when CURDATE() was executed. If you swapped the two values given for DATEDIFF(), the results would be positive. If you want to know only the number of days apart the two dates are, and don’t care which comes first, you can use ABS() with DATEDIFF() to get the absolute value no matter how you order them. Incidentally, although you may give values in date and time formats, only the date portions are used for determining the difference.

Similar to DATEDIFF(), you can get the difference between time values using the TIMEDIFF() function. Before looking at an example of it, let’s create a new table that uses dates and times. Suppose we’ve decided to organize and sponsor birding events, outings in which bird-watchers will go together to look for interesting birds. To store that information, we’ll create a table called birding_events in the birdwatchers database:

CREATE TABLE birding_events

(event_id INT AUTO_INCREMENT KEY,

event_name VARCHAR(255),

event_description TEXT,

meeting_point VARCHAR(255),

event_date DATE,

start_time TIME);

For the examples in this section, the column in this table with which we’re mostly concerned is start_time. Let’s add a birding event to birding_events by entering the following:

INSERT INTO birding_events

VALUES (NULL, 'Sandpipers in San Diego',

"Birdwatching Outing in San Diego to look for Sandpipers,

Curlews, Godwits, Snipes and other shore birds.

Birders will walk the beaches and surrounding area in groups of six.

A light lunch will be provided.",

"Hotel del Coronado, the deck near the entrance to the restaurant.",

'2014-06-15', '09:00:00');

Now we can try using TIMEDIFF(). Enter the following to determine how many days and how much time until the start of the event:

SELECT NOW(), event_date, start_time,

DATEDIFF(event_date, DATE(NOW())) AS 'Days to Event',

TIMEDIFF(start_time, TIME(NOW())) AS 'Time to Start'

FROM birding_events;

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

| NOW() | event_date | start_time |Days to Event| Time to Start |

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

| 2014-02-14 06:45:24 | 2014-06-15 | 09:00:00 | 121 | 02:14:36 |

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

The event will start in 121 days, 2 hours, 14 minutes, and 36 seconds from the time this SQL statement was executed. That’s correct, but the results displayed for Time to Start seem more like a time of day, rather than a count of hours, minutes, and seconds remaining. Let’s use DATE_FORMAT()for a nicer display. Let’s also use CONCAT() to put the number of days together with the time remaining:

SELECT NOW(), event_date, start_time,

CONCAT(

DATEDIFF(event_date, DATE(NOW())), ' Days, ',

DATE_FORMAT(TIMEDIFF(start_time, TIME(NOW())), '%k hours, %i minutes'))

AS 'Time to Event'

FROM birding_events;

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

| NOW() | event_date |start_time| Time to Event |

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

| 2014-02-14 06:46:25 | 2014-06-15 | 09:00:00 | 121 Days, 2 hours, 13 minutes |

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

You have to carefully check the parentheses on that statement to execute it successfully. We embed NOW() in the DATE() and TIME() functions. These in turn are embedded in DATEDIFF() and TIMEDIFF() to get the difference from the date and time stored in the database. TIMEDIFF() is embedded in DATE_FORMAT(), and all those functions are embedded in CONCAT().

After looking at these results, we decide that it would be much simpler if we change the table to use a single column to record the date and time of the event. I said in the first section of this chapter that we would cover some examples of how to change temporal data types for a column. Let’s do that now. Let’s create a new column, event_datetime, using the DATETIME data type:

ALTER TABLE birding_events

ADD COLUMN event_datetime DATETIME;

That adds the new column to contain the date and time. Now let’s update the table to combine them into event_datetime:

UPDATE birding_events

SET event_datetime = CONCAT(event_date,SPACE(1), start_time);

The CONCAT() function merges the date and time together as a string. MySQL will automatically convert that string into a date, and then set the value of event_datetime to a date and time value. Let’s execute a SELECT statement to see how the data looks now:

SELECT event_date, start_time, event_datetime

FROM birding_events;

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

| event_date | start_time | event_datetime |

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

| 2014-06-15 | 09:00:00 | 2014-06-15 09:00:00 |

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

The UPDATE worked fine. Let’s try now to get the formatting we want for the time remaining until the event, but from the new column. Enter the following:

SELECT NOW(), event_datetime,

CONCAT(DATEDIFF(event_datetime, NOW() ), ' Days, ',

TIME_FORMAT( TIMEDIFF( TIME(event_datetime), CURTIME() ),

'%k hours, %i minutes') )

AS 'Time to Event'

FROM birding_events;

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

| NOW() | event_datetime | Time to Event |

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

| 2014-02-14 05:48:55 | 2014-06-15 09:00:00 | 121 Days, 3 hours, 11 minutes |

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

That looks fine and it’s much better than having the date and time in separate columns. We can now alter birding_events to drop the two columns for date and time that we no longer need:

ALTER TABLE birding_events

DROP COLUMN event_date,

DROP COLUMN start_time;

We’ve successfully completed the process of migrating the date and time from two columns into one. You probably would have initially chosen to create one column instead of two, as we did in these examples. But you won’t always choose though the best temporal data type for a column. That’s why I wanted to walk you through the process of how to migrate between temporal data types: to prepare you for what to do when you don’t make the best choice the first time.

Summary

We’ve covered almost all of the date and time functions in MySQL and MariaDB in this chapter. There are only a few more. We skipped the aliases (e.g., ADDDATE() for DATE_ADD(), SUBDATE() for DATE_SUB()). There are also a few other functions for specialized needs, which you can learn as you need them. You’ve learned plenty in this chapter, and the information here should come in handy for many years.

The primary reason we went through so many date and time functions is because the date and time is a major part of most cultures: when something has happened, when something will happen, making appointments, and how much time has passed are common concerns when people interact with one another. This information is therefore a significant component of a database. I want you to be familiar with the temporal functions and to have a firm grasp on what tools are available. To that end, work through the exercises in the following section. You’ll retain more of what you learned in this chapter if you do.

Exercises

Here are some exercises to practice using date and time functions and a few of the string functions that we covered in Chapter 10. Some require you to use UPDATE to change the date values in tables. By updating data with date and time functions, you will gain a better understanding of the potential of these functions. The UPDATE statement is covered in Chapter 8.

1. Construct an SQL statement to select a list of members from the humans table who live in the United Kingdom. Select first and last names, concatenating them. Include the date they joined and when their membership expires. Use the DATE_FORMAT() function to format the result for each date to look like this: Sun., Feb. 2, 1979. Be sure to include all of the punctuations (i.e., the comma and the periods after the abbreviations, but not at the end, and the comma). Refer to Table 11-2 for the formatting codes.
When you’re finished, execute the SQL statement to check the results are correct. If they’re not, modify the statement until you get the right results.

2. Execute the SELECT statement to get a list of members and their expiration dates, ordering the results by membership_expiration. Then use the UPDATE statement to change the values in the membership_expiration column of the humans table. Use the ADDDATE() function to extend the membership of all members by 1 month and 15 days, but only for those whose membership has not yet expired as of June 30, 2014. Refer to Table 11-1 to find the interval codes you will need. You will also need to use a string in the WHERE clause. When finished, execute SELECT again and compare the results to the previous ones to confirm you were successful in changing the expiration dates for the correct members.
When you’ve finished extending the memberships, use DATESUB() to change membership_expiration to five days less for those same members as you did before. When that’s done, execute SELECT again and compare the results to the previous results.
Change the expiration date one more time, but this time use ADD_DATE() to change the expiration date to 10 days less. Remember, this will require you to use a negative value. After you’ve done that, execute SELECT again to check the results.

3. In Adjusting to Standards and Time Zones, we created a new table called bird_identification_tests. We added one row of data to it for testing. For this exercise, insert at least five more rows into that table. Make entries for two other human_id values and a few other bird_id values. While doing this, as shown in the example in that same section, enter a time value for id_start using CURTIME(), but enter NULL for id_end. Then run an UPDATE statement after each INSERT to set the time for id_end, using CURTIME() again so that the times will be different. Wait a short amount of time between the INSERT and the UPDATE for each row.
After you’ve entered several more rows to bird_identification_tests, construct a SELECT statement using the TIMEDIFF() function to compare the difference in the times of id_start and id_end for each row. Be sure to put the columns in the correct order within TIMEDIFF() so that the results do not show negative values. Include the first name of each person in the SQL statement. You’ll need to use JOIN to do that (covered in Joining Tables).

4. Put together another SELECT statement to get common_name from the birds table, and the id_start and id_end columns from the birdwatchers table. Use the TIMEDIFF() function to compare the differences in time between the two columns containing times. When you join the two tables, remember to adjust the JOIN to reflect that they are in separate databases. When that’s finished, execute the SELECT statement to be sure it’s constructed properly. Then add a GROUP BY clause to group by bird_id, and wrap TIMEDIFF() in AVG() to get the average time. Give that field an alias of Avg. Time or something similar. Run that statement to see the results. The results for the average time field should include a number with four decimal places, all zeros (e.g., 219.0000 for 2 minutes, 19 seconds).
Next, redo the SELECT statement to convert the average time from a number with four decimal places to the TIME format. To do this, first use the TRIM() function with the TRAILING option and give it a string of .0000 to trim that string from the end of the average time. Run the SELECT to see the results of that addition. Then, wrap all of that in LPAD() to make sure there’s enough zeros to conform to this format: hhmmss. Run the SELECT statement again to see the improvements in the results. Both of these string functions were covered in Trimming and Padding Strings.
Finally, use STR_TO_DATE() to convert the padded number (e.g., 000219) to a time. Refer to Table 11-2 to get the formatting codes for the hhmmss format. If you provide only formatting codes for time elements, STR_TO_DATE() will return only time information, which is what we want for this exercise. Execute the SELECT when you’re finished to make sure it’s correct. Make corrections until you get it to work.

5. Redo the SELECT you constructed successfully at the end of the previous exercise. Put what you assembled for the average time field into DATE_FORMAT(). Change the format to display like this: 01 minute(s), 21 seconds. When finished, execute the SQL statement. For extra points, use a string function to remove the leading zero for minutes, and when they occur, for the seconds. Use the IF() function to set minute or minutes as needed, and second and seconds.