MySQL – MariaDB – Date and Time Functions

How to create virtual machines with VirtualBox

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
, 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

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:


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

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


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.


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
. 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.


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
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


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


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

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
. 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):


| 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. The bird_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.


There are a few synonyms for the NOW() function:
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


| 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:


| 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()


| 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
( 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

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

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

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
(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
. The intervals given are
similar to the names of the date and time extraction functions we’ve
already reviewed: MONTH for month, HOUR for
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


Format for given values




‘dd hh’




‘dd hh:mm’


‘dd hh:mm:ss’































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

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)
| 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





Abbreviated weekday name



Abbreviated month name



Month (numeric)



Day of the month (numeric)



Day of the month with English suffix

(1st, 2nd, 3rd, etc.)


Day of the month (numeric)



Microseconds (numeric)









Minutes (numeric)






Day of the year









Month (numeric)



Month name



AM or PM

AM or PM


Time, 12-hour

(hh:mm:ss [AP]M)








Time, 24-hour



Week, where Monday is the first day of the



Week, where Sunday is the first day of the



Week, where Monday is the first day of the week; used
with `%x’



Week, where Sunday is the first day of the week; used
with `%X’



Day of the week



Weekday name



Year for the week, where Monday is the first day of
the week (numeric, four digits); used with `%v’



Year for the week, where Sunday is the first day of
the week (numeric, four digits); used with `%V’



Year (numeric, two digits)



Year (numeric, four digits)



A literal `%’

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


| %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


| %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():

   AS 'Date in Europe',
   AS 'Date in U.S.',
   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:


| 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

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:


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

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,
are DATE_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

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:

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
 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

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

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)
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)) =
                             '%Y%m') ),
                             '%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

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 the DATEDIFF() function in an SQL
statement similar to the following:

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

CREATE TABLE birding_events
 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

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

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,
   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

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

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.


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.


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

    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

  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

    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.

Comments are closed.