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:
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:
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:
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.
Contact Us