Comparing Date/Time Values
Build on your knowledge about temporal values in MySQL by learning about its capabilities for comparison.
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:
-- 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 TasksWHERE 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 denotes January ...