How the DATEDIFF() function works in Mariadb?

The MariaDB DATEDIFF() function is used to calculate the difference in days between two dates. It is commonly used in scenarios where you need to track the number of days between events, such as calculating age, determining the number of days until a deadline, or measuring the duration of an event.

Syntax

The syntax for the MariaDB DATEDIFF() function is as follows:

DATEDIFF(date1, date2)

date1 is the starting date.
date2 is the ending date.

The function returns the number of days between date1 and date2.

Examples

Example 1: Basic Date Difference

To find out how many days are between two dates:

SELECT DATEDIFF('2024-12-31', '2024-01-01') AS days_difference;

The output will be:

+-----------------+
| days_difference |
+-----------------+
|             365 |
+-----------------+

Example 2: Calculating Age

To calculate the age in days of a person born on a certain date:

SELECT DATEDIFF(CURDATE(), '1990-05-25') AS age_in_days;

The output will show the age in days:

+-------------+
| age_in_days |
+-------------+
|       12350 |
+-------------+

Example 3: Days Until an Event

To determine the number of days until a future event:

SELECT DATEDIFF('2024-12-25', CURDATE()) AS days_until_christmas;

The output will be the number of days until Christmas:

+----------------------+
| days_until_christmas |
+----------------------+
|                  283 |
+----------------------+

Example 4: Duration of an Event

To measure the duration of an event in days:

SELECT DATEDIFF('2024-03-20', '2024-03-17') AS event_duration;

The output will be the duration of the event:

+----------------+
| event_duration |
+----------------+
|              3 |
+----------------+

Example 5: Using `DATEDIFF()` with Table Data

If you need to calculate the difference in days between dates stored in a table:

DROP TABLE IF EXISTS project_milestones;
CREATE TABLE project_milestones (
    milestone_name VARCHAR(255),
    start_date DATE,
    end_date DATE
);
INSERT INTO project_milestones VALUES ('Project Start', '2024-01-01', '2024-12-31');

SELECT milestone_name, DATEDIFF(end_date, start_date) AS duration_in_days FROM project_milestones;

The output will display the duration in days for each milestone:

+----------------+------------------+
| milestone_name | duration_in_days |
+----------------+------------------+
| Project Start  |              365 |
+----------------+------------------+

Here are a few functions related to the MariaDB DATEDIFF() function:

MariaDB TIMEDIFF() function is used to calculate the difference in time between two time points.
MariaDB TIMESTAMPDIFF() function returns the difference between two timestamps, measured in a specified unit such as seconds, minutes, or hours.
MariaDB TO_DAYS() function converts a date to a day number, which can be useful for date calculations.

Conclusion

The DATEDIFF() function in MariaDB is a straightforward and efficient way to work with date differences. It simplifies the process of calculating the number of days between two dates, making it an invaluable tool for any database that deals with scheduling, reporting, or tracking time-sensitive data. By using DATEDIFF() in conjunction with other date and time functions, you can perform a wide range of date-related calculations with ease.