How To Work with Dates and Times in SQL

Introduction

When working with relational databases and Structured Query Language (SQL), there may be times when you need to work with values representing specific dates or times. For instance, you may need to calculate the total hours spent on a certain activity, or perhaps you need to manipulate date or time values using mathematical operators and aggregate functions to calculate their sum or average.

In this tutorial, you will learn how to use dates and times in SQL. You’ll begin by performing arithmetic and using various functions with dates and times using only the SELECT statement. Then you’ll practice by running queries on sample data, and you’ll learn how to implement the CAST function to make the output more digestible to read.

Prerequisites

To complete this tutorial, you will need:

• A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
• MySQL installed and secured on the server. Follow our How To Install MySQL on Ubuntu 20.04 guide to set this up. This guide assumes you’ve also set up a non-root MySQL user, as outlined in Step 3 of this guide.

Note: Please note that many relational database management systems use their own unique implementations of SQL. Although the commands outlined in this tutorial will work on most RDBMSs, the exact syntax or output may differ if you test them on a system other than MySQL.

To practice using date and time in this tutorial, you’ll need a database and table loaded with sample data. If you do not have one ready to insert, you can read the following Connecting to MySQL and Setting up a Sample Database section to learn how to create a database and table. This tutorial will refer to this sample database and table throughout.

Connecting to MySQL and Setting up a Sample Database

If your SQL database runs on a remote server, SSH into your server from your local machine:

ssh sammy@your_server_ip

Next, open the MySQL prompt, replacing sammy with your MySQL user account information:

mysql -u sammy -p

Create a database named datetimeDB:

CREATE DATABASE datetimeDB;

If the database was created successfully, you’ll receive the following output:

Query OK, 1 row affected (0.01 sec)

To select the datetimeDB database run the following USE statement:

USE datetimeDB;

Output:

Database changed

After selecting the database, create a table within it. For this tutorial’s example, we’ll create a table that holds two runners’ results for various races they’ve run in the span of a year. This table will hold the following seven columns:

• race_id: displays values of the int data type and serves as the table’s primary key, meaning each value in this column will function as a unique identifier for its respective row.
• runner_name: uses the varchar data type with a maximum of 30 characters for the names of the two racers, Bolt and Felix.
• race_name: holds the types of races with the varchar data type at a maximum of 20 characters.
• start_day: uses the DATE data type to track the date of a specific race by year, month, and day. This data type adheres to the following parameters: four digits for the year, and a maximum of two digits for the month and day (YYYY-MM-DD).
• start_time: represents the race start time with the TIME data type by hours, minutes, and seconds (HH:MM:SS). This data type follows a 24-hour clock format, such as 15:00 for the equivalent of 3:00 pm.
• total_miles: shows the total mileage for each race using the decimal data type since many of the total miles per race are not whole numbers. In this case, decimal specifies a precision of three with a scale of one, meaning that any values in this column can have three digits, with one of those digits being to the right of the decimal point.
• end_time: uses the TIMESTAMP data type to track the runners’ times at the end of the race. This data type combines both date and time in one string, and its format is a combination of those of DATE and TIME: (YYYY-MM-DD HH:MM:SS).

Create the table by running the CREATE TABLE command:

CREATE TABLE race_results (
race_id int, 
runner_name varchar(30),
race_name varchar(20), 
start_day DATE,
start_time TIME, 
total_miles decimal(3, 1),
end_time TIMESTAMP,
PRIMARY KEY (race_id)
);

Next insert some sample data into the empty table:

INSERT INTO race_results
(race_id, runner_name, race_name, start_day, start_time, total_miles, end_time)
VALUES
(1, 'bolt', '1600_meters', '2022-09-18', '7:00:00', 1.0, '2022-09-18 7:06:30'),
(2, 'bolt', '5K', '2022-10-19', '11:00:00', 3.1, '2022-10-19 11:22:31'),
(3, 'bolt', '10K', '2022-11-20', '10:00:00', 6.2, '2022-11-20 10:38:05'),
(4, 'bolt', 'half_marathon', '2022-12-21', '6:00:00', 13.1, '2022-12-21 07:39:04'),
(5, 'bolt', 'full_marathon', '2023-01-22', '8:00:00', 26.2, '2023-01-22 11:23:10'),
(6, 'felix', '1600_meters', '2022-09-18', '7:00:00', 1.0, '2022-09-18 7:07:15'),
(7, 'felix', '5K', '2022-10-19', '11:00:00', 3.1, '2022-10-19 11:30:50'),
(8, 'felix', '10K', '2022-11-20', '10:00:00', 6.2, '2022-11-20 11:10:17'),
(9, 'felix', 'half_marathon', '2022-12-21', '6:00:00', 13.1, '2022-12-21 08:11:57'),
(10, 'felix', 'full_marathon', '2023-01-22', '8:00:00', 26.2, '2023-01-22 12:02:10');

Output:

Query OK, 10 rows affected (0.00 sec)

Once you’ve inserted the data, you’re ready to begin practicing some arithmetic and functions with date and time in SQL.

Using Arithmetic with Dates and Times

In SQL, you can manipulate date and time values using mathematical expressions. All that’s required is the mathematical operator and the values you want to calculate.

As an example, say you wanted to find one date that is a certain number of days after another. The following query takes one date value (2022-10-05) and adds 17 to it to return the value for the date seventeen days after the one specified in the query. Note that this example specifies 2022-10-05 as a DATE value to ensure that the DBMS won’t interpret it as a string or some other data type:

SELECT DATE '2022-10-05' + 17 AS new_date;

Output:

+----------+
| new_date |
+----------+
| 20221022 |
+----------+
1 row in set (0.01 sec)

As this output indicates, 17 days after 2022-10-05 is 2022-10-22, or October 22, 2022.

As another example, say you want to calculate the total hours between two different times. You can do this by subtracting the two times from one another. For the following query, 11:00 is the first time value and 3:00 is the second time value. Here you’ll need to specify that both are TIME values in order to return the difference in hours:

SELECT TIME '11:00' - TIME '3:00' AS time_diff;

Output:

+-----------+
| time_diff |
+-----------+
|     80000 |
+-----------+
1 row in set (0.00 sec)

This output tells you that the difference between 11:00 and 3:00 is 80000, or 8 hours.

Now practice using arithmetic on the date and time information from the sample data. For the first query, calculate the total time it took the runners to finish each race by subtracting end_time from the start_time:

SELECT runner_name, race_name, end_time - start_time 
AS total_time 
FROM race_results;

Output:

+-------------+---------------+----------------+
| runner_name | race_name     | total_time     |
+-------------+---------------+----------------+
| bolt        | 1600_meters   | 20220918000630 |
| bolt        | 5K            | 20221019002231 |
| bolt        | 10K           | 20221120003805 |
| bolt        | half_marathon | 20221221013904 |
| bolt        | full_marathon | 20230122032310 |
| felix       | 1600_meters   | 20220918000715 |
| felix       | 5K            | 20221019003050 |
| felix       | 10K           | 20221120011017 |
| felix       | half_marathon | 20221221021157 |
| felix       | full_marathon | 20230122040210 |
+-------------+---------------+----------------+
10 rows in set (0.00 sec)

You’ll notice that this output in the total_time column is rather long and difficult to read. Later on, we’ll demonstrate how to use the CAST function to convert these data values so that they’re clearer to read.

Now, if you were only interested in each runner’s performance for longer races, such as the half and full marathons, you can query your data to retrieve that information. For this query, subtract end_time from start_time, and narrow down your results by using the WHERE clause to retrieve data where total_miles were greater than 12:

SELECT runner_name, race_name, end_time - start_time AS half_full_results
FROM race_results 
WHERE total_miles > 12;

Output:

+-------------+---------------+-------------------+
| runner_name | race_name     | half_full_results |
+-------------+---------------+-------------------+
| bolt        | half_marathon |    20221221013904 |
| bolt        | full_marathon |    20230122032310 |
| felix       | half_marathon |    20221221021157 |
| felix       | full_marathon |    20230122040210 |
+-------------+---------------+-------------------+
4 rows in set (0.00 sec)

Using Date and Time Functions and Interval Expressions

There are several functions that can be used to find and manipulate date and time values in SQL. SQL functions are typically used to process or manipulate data, and the functions available depend on the SQL implementation. Most SQL implementations, however, allow you to find the current date and time by querying for the current_date and current_time values.

To find today’s date, for example, the syntax is short and comprised of only the SELECT statement and the current_date function as in the following:

SELECT current_date;

Output:

+--------------+
| current_date |
+--------------+
| 2022-02-15   |
+--------------+
1 row in set (0.00 sec)

Using the same syntax, you can find the current time with the current_time function:

SELECT current_time;

Output:

+--------------+
| current_time |
+--------------+
| 17:10:20     |
+--------------+
1 row in set (0.00 sec)

If you prefer to query for both date and time in the output, use the current_timestamp function:

SELECT current_timestamp;

Output:

+---------------------+
| current_timestamp   |
+---------------------+
| 2022-02-15 19:09:58 |
+---------------------+
1 row in set (0.00 sec)

You can use date and time functions like these within arithmetic functions similar to the previous section. For example, say you want to know what the date was 11 days ago from today’s date. In this case, you could use the same syntax structure you used previously to query the current_date function and then subtract 11 from it to find the date from eleven days ago:

SELECT current_date - 11;

Output:

+-------------------+
| current_date - 11 |
+-------------------+
|          20220206 |
+-------------------+
1 row in set (0.01 sec)

As this output indicates, 11 days ago from the current_date (at the time of this writing) was 2022-02-06, or February 6, 2022. Now try running this same operation, but replace current_date with the current_time function:

SELECT current_time - 11;

Output:

+-------------------+
| current_time - 11 |
+-------------------+
|        	233639 |
+-------------------+
1 row in set (0.00 sec)

This output shows that when you subtract 11 from the current_time value, it subtracts 11 seconds. The operation you ran previously using the current_date function interpreted 11 as days, not seconds. This inconsistency in how numbers are interpreted when working with date and time functions can be confusing. Instead of requiring you to manipulate date and time values using arithmetic like this, many database management systems let you be more explicit through the use of INTERVAL expressions.

INTERVAL expressions allow you to find what the date or time would be before or after a set interval from a given date or time expression. They must take the following form:

Example interval expression:
INTERVAL value unit

For instance, to find the date five days from now, you could run the following query:

SELECT current_date + INTERVAL '5' DAY AS "5_days_from_today";

The query finds the current_date value and adds the interval expression INTERVAL ‘5’ DAY to it, returning the date 5 days from now:

+-------------------+
| 5_days_from_today |
+-------------------+
| 2022-03-06    	|
+-------------------+
1 row in set (0.00 sec)

This is much less ambiguous than the following query, which produces similar, though not identical output:

SELECT current_date + 5 AS "5_days_from_today";

Output:

+-------------------+
| 5_days_from_today |
+-------------------+
|      	20220306 |
+-------------------+
1 row in set (0.00 sec)

Note that you can also subtract intervals from dates or times to find values from before the specified date value:

SELECT current_date - INTERVAL '7' MONTH AS "7_months_ago";

Output:

+--------------+
| 7_months_ago |
+--------------+
| 2021-08-01   |
+--------------+
1 row in set (0.00 sec)

What units are available for you to use in INTERVAL expressions depends on your choice of DBMS, though most will have options like HOUR, MINUTE, and SECOND:

SELECT current_time + INTERVAL '6' HOUR AS "6_hours_from_now",
current_time - INTERVAL '5' MINUTE AS "5_minutes_ago",
current_time + INTERVAL '20' SECOND AS "20_seconds_from_now";

Output:

+------------------+---------------+---------------------+
| 6_hours_from_now | 5_minutes_ago | 20_seconds_from_now |
+------------------+---------------+---------------------+
| 07:51:43     	| 01:46:43  	| 01:52:03.000000 	|
+------------------+---------------+---------------------+
1 row in set (0.00 sec)

Using CAST and Aggregate Functions with Date and Time

Recall from the third example in the Using Arithmetic with Dates and Times section, when you ran the following query to subtract end_time from start_time to calculate the total hours each runner completed per race. The output, however, resulted in a column containing a very long output, which follows the TIMESTAMP data type that was set up in the table:

SELECT runner_name, race_name, end_time - start_time 
AS total_time 
FROM race_results;

Output:

+-------------+---------------+----------------+
| runner_name | race_name     | total_time     |
+-------------+---------------+----------------+
| bolt        | 1600_meters   | 20220918000630 |
| bolt        | 5K            | 20221019002231 |
| bolt        | 10K           | 20221120003805 |
| bolt        | half_marathon | 20221221013904 |
| bolt        | full_marathon | 20230122032310 |
| felix       | 1600_meters   | 20220918000715 |
| felix       | 5K            | 20221019003050 |
| felix       | 10K           | 20221120011017 |
| felix       | half_marathon | 20221221021157 |
| felix       | full_marathon | 20230122040210 |
+-------------+---------------+----------------+
10 rows in set (0.00 sec)

Because you’re performing an operation with two columns that have different data types (end_time holding TIMESTAMP values and start_time holding TIME values), the database doesn’t know what data type to use when it prints the result of the operation. It converts both values to integers to perform the operation, resulting in long numbers in the total_time column.

To help make this data clearer to read and interpret, you can use the CAST function to convert these long integer values to the TIME data type. To do so, start with CAST and then follow it immediately with an opening parenthesis, the values you want converted, and then the AS keyword and the data type you want to convert it to.

This query is identical to the previous one, but uses the CAST function to convert total_time to time:

SELECT runner_name, race_name, CAST(end_time - start_time AS time)
AS total_time 
FROM race_results;

Output:

+-------------+---------------+------------+
| runner_name | race_name     | total_time |
+-------------+---------------+------------+
| bolt        | 1600_meters   | 00:06:30   |
| bolt        | 5K            | 00:22:31   |
| bolt        | 10K           | 00:38:05   |
| bolt        | half_marathon | 01:39:04   |
| bolt        | full_marathon | 03:23:10   |
| felix       | 1600_meters   | 00:07:15   |
| felix       | 5K            | 00:30:50   |
| felix       | 10K           | 01:10:17   |
| felix       | half_marathon | 02:11:57   |
| felix       | full_marathon | 04:02:10   |
+-------------+---------------+------------+
10 rows in set (0.00 sec)

CAST converted the data values to TIME in this output, making it much more digestible to read and understand.

Let’s use aggregate functions with the CAST function to find each runner’s shortest, longest, and total time results. First, query for the minimum (or shortest) amount of time spent with the MIN aggregate function. Again, you’ll want to use CAST to convert the TIMESTAMP data values to TIME data values for clarity. When using two functions, two pairs of parentheses are required, with the total hours calculation nested in one. Add a GROUP BY clause to organize values by runner_name and present each runner’s race results:

SELECT runner_name, MIN(CAST(end_time - start_time AS time)) AS min_time
FROM race_results GROUP BY runner_name;

Output:

+-------------+----------+
| runner_name | min_time |
+-------------+----------+
| bolt        | 00:06:30 |
| felix       | 00:07:15 |
+-------------+----------+
2 rows in set (0.00 sec)

This output shows each runner’s shortest run-time, in this case a minimum of six minutes and 30 seconds for Bolt, and seven minutes and 15 seconds for Felix.

Next, find each runner’s longest run-time. You can use the same syntax as the previous query, but this time replace MIN with MAX:

SELECT runner_name, MAX(CAST(end_time - start_time AS time)) AS max_time
FROM race_results GROUP BY runner_name;

Output:

+-------------+----------+
| runner_name | max_time |
+-------------+----------+
| bolt        | 03:23:10 |
| felix       | 04:02:10 |
+-------------+----------+
2 rows in set (0.00 sec)

This output tells us that Bolt’s longest run-time was a total of three hours, 23 minutes, and 10 seconds. Felix lpngest run- time was a total of four hours, two minutes, and 10 seconds.

Now let’s query for some high-level information about the total hours each runner spent running. For this query, combine the SUM aggregate function to find the total sum of hours based on end_time – start_time, and use CAST to convert those data values to TIME. Don’t forget to include GROUP BY to organize the values for both runner’s results:

SELECT runner_name, SUM(CAST(end_time - start_time AS time))
AS total_hours FROM race_results GROUP BY runner_name;

Output:

+-------------+-------------+
| runner_name | total_hours |
+-------------+-------------+
| bolt        |       52880 |
| felix       |       76149 |
+-------------+-------------+
2 rows in set (0.00 sec)

Interestingly, this output shows the interpretation for MySQL, which is actually calculating the total time as integers. Reading these results as time, Bolt’s total is 5 hours, 28 minutes, 80 seconds, and Felix’s is 7 hours, 61 minutes. This time breakdown doesn’t make sense, indicating it’s calculated as an integer, not time. If you tried this in a different DBMS, such as PostgreSQL, for example, the same query would look slightly different:

SELECT runner_name, SUM(CAST(end_time - start_time AS time))
AS total_hours FROM race_results GROUP BY runner_name;

Output:

 runner_name | total_hours
-------------+-------------
 felix       | 10:01:44
 bolt        | 06:09:20
(2 rows)

In PostgreSQL, the query interprets values as time, showing Felix’s 10 hours, 1 minute, and Bolt’s 6 hours, 9 minutes. This example shows how different DBMS implementations can interpret data values differently, even with the same query.

Conclusion

Understanding date and time in SQL is useful for querying specific results like minutes, seconds, hours, days, and months. Additionally, many functions for dates and times make it easier to find values like the current date or time. While this tutorial used only addition and subtraction on dates and times in SQL, you can use any mathematical expression. Learn more from our guide on mathematical expressions and aggregate functions, and apply them to date and time queries.