MariaDB DATEDIFF() Function

Sometimes, the difference between two dates in MariaDB is achieved through the DATEDIFF() function. The DATEDIFF() function offers a straightforward solution for measuring the gap between dates, whether it’s for scheduling tasks, tracking project durations, or analyzing trends over time. In this article, we’ll learn about the DATEDIFF() function by exploring its syntax and examples to master date manipulation in our MariaDB database.

How does the DATEDIFF() Function work?

The DATEDIFF() function is a function that is used to calculate the difference between two dates. It returns the difference in days between two date values. The syntax for the DATEDIFF() function is as follows:

Syntax:

DATEDIFF( date1,date2)

Explanation: This function takes two parameters i.e. date strings, computes the difference between two dates, and returns an absolute value. The function considers only the date part of the string and ignores the time or timestamp when used in the function. Some important points while using the DATEDIFF() function are.

• The value returned by the DATEDIFF() function can be positive, negative, or 0.
• If the arguments given in the function are null or invalid, the function returns null.
• The date parameters should be given in the format ‘YYYY-MM-DD’ (year, month, day).
• The function returns an absolute negative integer when date2 is later than date1.
• The function returns an absolute positive integer when date1 is later than date2.

Examples of MariaDB DATEDIFF() Function

To understand the DATEDIFF() Function in MariaDB we need a table on which we will perform various operations and queries. So we have an employees table which consists of employee_id, employee_name, and join_date as Columns. The table is shown below.

employee_id	employee_name	join_date
1	John Doe	2023-01-15
2	Jane Smith	2022-05-20
3	Michael Johnson	2024-02-10
4	Emily Davis	2023-11-08
5	Alex Brown	2022-09-03

Example 1: Calculate The Number of Days

Let’s calculate the number of days since each employee joined the company.

Query:

SELECT employee_name, DATEDIFF('2024-02-14', join_date)  AS  days_in_company  FROM EmployeeJoinDates;

Output:

number of days in the company

Explanation: The above query is used to select the employee names and the number of days they worked in the company, which is calculated as the difference between their join dates and ‘2024-02-14′. The function returns an absolute positive integer which is the number of days an employee worked in the company from their join date till ‘2024-02-14‘.

Example 2: Employeed Who Joined More Than 365 Days Ago

Let’s Find employees who joined more than 365 days ago in

Try any of the query, either use CURRENT_DATE() function or NOW() function, the result obtaines will be same.

Query:

SELECT employee_name   FROM EmployeeJoinDates   WHERE DATEDIFF(CURRENT_DATE(), join_date) > 365;
##OR
SELECT employee_name   FROM EmployeeJoinDates   WHERE DATEDIFF(NOW(), join_date) > 365;

Output:

DATEDIFF() and CURRENT_DATE()

Explanation: The above query is used for finding the employees who joined more than 365 days ago i.e. we just need to calculate the date difference ( between the employee join date and current date ) and retrieve the records with date difference more than 365.

Example 3: INVALID ARGUMENTS

Let’s see what happens if we use some string as a parameter or use only year or month as a parameter for the function.

Query:

SELECT DATEDIFF('2024-02-11', 'abc') AS DateDifference;                #Non-Date Parameters
SELECT DATEDIFF('2024', '2023-11')  AS DateDifference;                   #Missing parts

Output:

Invalid parameters

Explanation: The function takes only two parameters in the format ‘YYYYMMDD‘, so if there is only one argument or missing parts, the function returns NULL. Similarly, if the arguments are invalid strings like names or characters, the function returns NULL.

Conclusion

Overall, In this article we have learned about the DATEDIFF() Function which make it a valuable tool for various applications from tracking project timelines to managing subscription durations. we have saw various examples which helps us to understand the DATEDIFF() function in easy manner.