Mastering Advanced SQL Techniques with MySQL/

...

/

Modifying Date/Time Values

Modifying Date/Time Values

Understand the functions that MySQL provides for modifying temporal values.

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:

Press + to interact
-- 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 second signature ...

Access this course and 1400+ top-rated courses and projects.