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

Modifying Date/Time Values

Explore MySQL functions to modify date, time, and timestamp values efficiently. Learn to add or subtract intervals with ADDDATE and DATE_ADD, handle time calculations using ADDTIME, and convert time zones using CONVERT_TZ to manage temporal data accurately.

We'll cover the following...

Creating and accessing values with temporal data types in MySQL only gets us so far. While we can update a temporal value by overwriting it with the most recent date or time, this approach is only helpful in a few use cases. Let us consider the scenario from a prior example where we store tasks with a due date:

MySQL
-- Generate a table that records tasks with due dates.
CREATE TABLE Tasks
(
    id          INT AUTO_INCREMENT PRIMARY KEY,
    title       TEXT,
    description TEXT,
    due_date    DATE
);
-- Record tasks with due dates.
INSERT INTO Tasks (title, description, due_date)
VALUES ('Lesson Draft', 'Create a draft for the lesson.', CURDATE()),
       ('Grocery Shopping', 'Go grocery shopping for this evening''s dinner.', CURDATE()),
       ('Pay Rent', 'Pay the rent for this month.', '2022-06-01'),
       ('Prepare Presentation', 'Prepare for the presentation at the university this week.', '2022-05-30'),
       ('New Apartment', 'Start looking for a new apartment as the current contract ends soon.', '2023-02-01');
-- Inspect the recorded tasks.
TABLE Tasks;

Assuming that software providing a user interface for the due tasks interacts with this data, we could provide a function that allows a user to postpone a specific task by a given time interval. In this case, it is easier for us as software engineers to modify the due date of a task by the selected time interval rather than manually calculating the modified due date and storing it in our database. To our advantage, MySQL offers a range of functions that enable us to easily modify temporal values.

Modify date values

Taking the due_date from the example above, MySQL provides different options for adding a time interval to the value. Firstly, there is ADDDATE(date, days), which takes a date and several days to add to date. Although this may already be sufficient for some use cases, a ...