How to work with dates and times in SQL

  • 488 views
  • 12 minute read
Hadi Bahadori
  • July 25, 2024
0 Shares
0
0
0
0
0
Shares
0
0
0
0

Introduction

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

In this tutorial, you'll learn how to use dates and times in SQL. You'll start by performing calculations and using various functions with dates and times using just the SELECT statement. Then you'll practice by running queries on sample data and learn how to implement the CAST function to make the output more digestible for reading.

Prerequisites

To complete this tutorial, you will need:

  • A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and an enabled firewall.
  • MySQL is installed and secured on the server.

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

To practice using dates and times in this tutorial, you will need a database and table loaded with sample data. If you don't have one ready to insert, you can read the 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

Then, open the MySQL prompt and replace Sami with your MySQL account information:

mysql -u sammy -p

Create a database called datetimeDB:

CREATE DATABASE datetimeDB;

If the database is created successfully, you will receive the following output:

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, we will create a table that holds the results of two runners for different races they have run over the course of a year. This table has the following seven columns:

  • race_id: Represents values of data type int and acts as the primary key of the table, meaning that each value in this column acts as a unique identifier for the corresponding row.
  • runner_name: Uses varchar data type with a maximum of 30 characters for the names of the two runners Bolt and Felix.
  • race_name: Holds race types with varchar data type up to 20 characters.
  • start_day: Uses the DATE data type to track the date of a specific match based on 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 start time of the match with the TIME data type in hours, minutes, and seconds (HH:MM:SS). This data type follows the 24-hour clock format, such as 15:00 for the equivalent of 3:00 PM.
  • total_miles: Displays the total mileage for each race using the decimal data type because many of the total miles per race are not whole numbers. In this case, the decimal specifies a precision of three with a scale of one, meaning that each value 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 time of runners at the end of the race. This data type combines the date and time into a string, and its format is a combination 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) );

Then enter 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) Records: 10 Duplicates: 0 Warnings: 0

Once you have entered the data, you are ready to start practicing arithmetic and functions with dates and times in SQL.

Using the account with date and time

In SQL, you can manipulate date and time values using mathematical expressions. All you need is a mathematical operator and the values you want to calculate.

For example, suppose you want to find a date that is a specified number of days after another. The following query takes a date value (2022-10-05) and adds 17 to it to return a date value seventeen days after the date specified in the query. Note that this example specifies 2022-10-05 as the DATE value to ensure that the DBMS does not interpret it as a string or 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 shows, 17 days after 2022-10-05 is 2022-10-22, or October 22, 2022.

As another example, let's say you want to calculate the total hours between two different times. You can do this by subtracting two times from each other. For the following query, 11:00 is the first time value and 3:00 is the second time value. Here you need to specify that both values are TIME values 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 80,000, or 8 hours.

Now practice using calculations on 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 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 quite long and difficult to read. Next, we'll show you how to use the CAST function to transform 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 half and full marathons, you could query your data to retrieve that information. For this query, subtract end_time from start_time, and restrict your results using a WHERE clause to retrieve data where total_miles was 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)

In this section, you performed some date and time calculations with the SELECT statement and for practical purposes on sample data. Next, you will practice queries using various date and time functions.

Using date and time functions and range 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 available functions depend on the SQL implementation. However, most SQL implementations allow you to find the current date and time by querying for the values current_date and current_time.

To find today's date, for example, the syntax is short and consists of just the SELECT statement and the current_date function, like this:

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 the 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 in arithmetic functions similar to the previous section. For example, say you want to know what the date was 11 days ago from today. In this case, you can use the same syntax you used to query the current_date function, and then subtract 11 from it to find the date eleven days ago:

SELECT current_date - 11;

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

As this output shows, 11 days before current_date (at the time of writing this article) was 2022-02-06, or February 6, 2022. Now try the 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 performed earlier using the current_date function interpreted 11 as days, not seconds. This inconsistency in how numbers are interpreted can be confusing when working with date and time functions. Rather than requiring you to manipulate date and time values using calculations like this, many database management systems allow you to be more explicit through the use of INTERVAL statements.

INTERVAL expressions allow you to specify a date or time before or after a specified interval of time from a date or time expression. They must have the following form:

INTERVAL value unit

For example, to find the date five days from now, you can run the following query:

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

This example finds the value of current_date and then adds the interval expression INTERVAL '5' DAY to it. This returns the date 5 days in the future:

Output +------------------+ | 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 subtract time intervals from dates or times to find values before a 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 use in INTERVAL statements depends on your choice of DBMS, although most will have options such as 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)

Now that you've learned about interval expressions and some date and time functions, continue practicing working with the sample data you entered in the first step.

Using CAST and Aggregate functions with dates and times

Recall 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 ran in each race. However, the output resulted in a column containing a very long output that followed the TIMESTAMP data type set 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 are performing an operation on two columns that have different data types (end_time holds TIMESTAMP values and start_time holds TIME values), the database doesn't know what data type to use when printing the result of the operation. Instead, it converts both values to integers in order 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 this, start with CAST and then immediately follow it with an open parenthesis, the values you want to convert, and then the AS keyword and the data type you want to convert it to.

The following query is identical to the previous example, but uses a CAST function to convert the total_time column to a time data type:

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 in this output to TIME, making it very digestible to read and understand.

Now, let's use some aggregate functions in combination with the CAST function to find the shortest, longest, and total time of each runner's results. First, look for the minimum (or shortest) time spent with the MIN function. Again, you want to use CAST to convert the TIMESTAMP data values to TIME data values for clarity. Please note that when using two functions like in this example, two pairs of parentheses are required, and the calculation of the total hours (end_time - start_time) must be placed in one of them. Finally, add a GROUP BY clause to organize these values by the runner_name column to display the runner's race results output:

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 the shortest time of the runner, in this case at least six minutes and 30 seconds for Bolt and seven minutes and 15 seconds for Felix.

Next, find the longest execution time for each runner. You can use the same syntax as the previous statement, 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. And Felix's was a total of four hours, two minutes, and 10 seconds.

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

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 MySQL interpreting the total time as an integer. If we read these results as times, Bolt's total time would be divided into five hours, 28 minutes, and 80 seconds. And Felix's time would be divided into seven hours, 61 minutes, and 49 seconds. As you can tell, this is not a logical time breakdown, indicating that it is being calculated as an integer and not a time. For example, if you try this in a different DBMS, such as PostgreSQL, the same query would look a little 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 this case, the query in PostgreSQL interprets the values as times and calculates them as such, so that Felix's results break down to a total of 10 hours, one minute, and 44 seconds. And Bolt's is six hours, nine minutes, and 20 seconds. This is an example of how different DBMS implementations may interpret data values differently, even if they use the same query and dataset.

Result

Understanding how to use dates and times in SQL is useful when searching for specific results such as minutes, seconds, hours, days, months, years. Or a combination of all of them. In addition, there are many functions for dates and times that make it easier to find specific values such as the current date or time. While this tutorial only covers addition and subtraction calculations on dates and times in SQL, you can use date and time values with any mathematical expression. Learn more in our guide to mathematical expressions and addition functions and try them out with your date and time searches.

[Total: 1   Average: 5/5]
In this article:
,
Post written by:
Follow
Leave a Reply

Your email address will not be published. Required fields are marked *

Previous Article
Next Article
You May Also Like