Search⌘ K
AI Features
Home
Courses
Mastering Advanced SQL Techniques with MySQL

Comparing Date/Time Values

Explore various MySQL functions to compare date, time, and datetime values effectively. Understand how to use DATEDIFF, TIMEDIFF, PERIOD_DIFF, and TIMESTAMPDIFF to filter and analyze temporal data, including tasks due within specific periods, enhancing your ability to manage time-based data efficiently.

We'll cover the following...

Creating, accessing, and modifying temporal values in MySQL covers many of the functions provided for date and time. However, the comparison remains a fourth use case for working with temporal values. By its very nature, comparisons of temporal values are especially common in an SQL statement’s WHERE clause:

MySQL
-- Inspect the recorded tasks.
TABLE Tasks;
-- Insert empty lines in the output for better readability.
SELECT "";
-- Retrieve all tasks that are due next year.
SELECT title AS `Title`, description AS `Description`
FROM Tasks
WHERE due_date >= '2023-01-01';

Here, we refer to a prior example where we record tasks with a due date. In particular, we want to find out about tasks that are due in 2023. For that purpose, we use the comparison operator >= applied to values of the type DATE. While sufficient in this case, more complicated scenarios demand rich SQL functions for the comparison of temporal values. For example, how would we determine the tasks due during the next seven days?

Compare period values

Generally speaking, a period refers to an interval of time spanned by two distinct points in time, e.g., a day starts at 00:00:00 o’clock and ends at 23:59:59 o’clock. In MySQL, a period is not a data type of its own. It is merely a format defined for integers in the context of PERIOD_DIFF(). An integer in the format of YYMM or YYYYMM is said to be a period, e.g., 2201 ...