What is datediff in MySQL

MySQL is one of the most popular databases in the world. Regardless of the industry, MySQL is widely adopted for its features. It’s an open-source RDBMS (Relational Database Management System). Data are organized into tables that can be related to each other. It incorporates SQL to perform various database actions.

In this guide, we’ll check out one such function. It will demonstrate how to use the DATEDIFF function in MySQL.

The DATEDIFF() in SQL

The DATEDIFF function is available as a part of the SQL query language. In MySQL, the function DATEDIFF() takes two dates as input, calculates the difference, and returns the number of dates between the two dates.

Here’s how the basic structure of the function looks like.

$ DATEDIFF(expression_1,expression_2)

Here,

expression_1: The first date
expression_2: The second date

The expression can be of any of the following formats.

time
date
datetime
datetime2
smalldatetime
datetimeoffset

In standard SQL, however, DATEDIFF() is slightly different. It also supports an additional parameter to specify the date part to work on.

$ DATEDIFF(date_part,expression_1,expression_2)

Here,

date_part: Describes what date part the function should calculate on. By default, the value is set to days. However, it also supports additional values. These date_part values also have suitable abbreviations.

month (“mm” or “m”)
year (“yy” or “yyyy”)
quarter (“qq” or “q”)
day (“dd” or “d”)
week (“wk” or “ww”
dayofyear (“dy” or “y”)
hour (“hh”)
minute (“mi” or “m”)
second (“ss” or “s”)
millisecond (“ms”)
microsecond (“mcs”)
nanosecond (“ns”)

The function DATEDIFF() also comes with a range limitation. The calculated value of the date difference must be within the range of integer (-2,147,483,647 to 2,147,483,648).

Usage of DATEDIFF()

In this section, we’ll check out various ways of using the function. For demonstration, a MySQL server is set with phpMyAdmin for ease of use. Check out this guide on configuring phpMyAdmin on Ubuntu.

Basic usage

From the SQL tab, run the following query.

$ SELECT DATEDIFF('2090-10-11', '2020-10-10') AS 'Result';

As the output shows, the difference between the dates specified is 25568 days.

Comparing with an earlier date

What if the second date was later than the first date? Swap the values and test them out.

$ SELECT DATEDIFF('2020-10-10', '2090-10-11') AS 'Result';

As we can see, it still shows 25568 days. However, the value is negative. It’s a crucial difference to keep in mind when implementing this function in any SQL script/query.

Datetime values

The DATEDIFF() function also accepts datetime values as the parameter. The time values are expected to be in 24-hour format.

$ SELECT DATEDIFF('2090-10-11 23:59:59', '2020-10-10 00:00:00') AS 'Result_1';

$ SELECT DATEDIFF('2090-10-11 00:00:00', '2020-10-10 23:59:59') AS 'Result_2';

Note that the additional time value doesn’t impact the result of the calculation. The function only focuses on the date.

Working with wrong date values

If the date values are wrong, then DATEDIFF() will return the NULL value. To showcase, enter an invalid date to either of the parameters.

$ SELECT DATEDIFF('2099-99-99', '2020-20-20') AS 'Result'

As expected, the return value is NULL.

Combining DATEDIFF() with CURDATE()

The function CURDATE() returns the current date of the machine. It takes no parameter. Learn more on using CURDATE() to insert current date and time in MySQL.

Using CURDATE(), we can find the difference between the present and the target date. For example, let’s compare the current date against a day in the past.

$ SELECT DATEDIFF(CURDATE(), '1980-10-10') AS 'Result'

$ SELECT DATEDIFF('2077-01-01', CURDATE()) AS 'Result'

Note that there are additional functions, for example, CURRENT_DATE(), that act the same way as CURDATE(). In situations, they both can be interchanged.

$ SELECT DATEDIFF(CURRENT_DATE(), '1980-10-10') AS 'Result'

Using DATEDIFF() with tables

So far, we’ve implemented simple DATEDIFF() commands to demonstrate its usages. It’s time to put it into action.

I’ve grabbed a sample database containing various info about a certain company and its employees for demonstration. The sample database is directly available from here. We’ll select the first and last names of the employees from this database and find out how long they have been working until now.

$ SELECT first_name, last_name, DATEDIFF(CURDATE(), hire_date) AS 'days worked' FROM employees;

Final thoughts

This guide successfully demonstrates the usage of the DATEDIFF() function in MySQL. It calculates the difference between two dates and returns the value as the number of days. All the demonstrations help understand the working process of the DATEDIFF function.

To learn more about MySQL, check these guides on creating tables, renaming tables, managing user privileges, etc.

Happy computing!