What is dateiff in MySQL

MySQL is one of the most popular databases in the world. Regardless of the industry, MySQL is widely used for its features. It is an open source DBMS (relational database management system). Data is organized into tables that can be linked to each other. It includes SQL to perform various actions on the database.

In this article, we’ll take a look at one of those features. He will demonstrate how to use the DATEDIFF function in MySQL.

DATEDIFF () in SQL

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

This is what the basic structure of a function looks like.

$DATEDIFF( выражение_1; выражение_2 )

Here:

  • expression_1: first expression
  • expression_2: second expression

The expression can be in any of the following formats.

  • time
  • date
  • datetime
  • datetime2
  • smalldatetime
  • datetimeoffset

However, in standard SQL, DATEDIFF () is slightly different. It also supports an additional parameter for specifying the part of the date to work on.

$DATEDIFF(date_part, выражение_1, выражение_2)

Here, date_part: describes which part of the date the function should calculate. The default is days. However, it also supports complementary 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)
  • day of the year (“dy” or “y”)
  • hour (“hh”)
  • minute (“mi” or “m”)
  • second (“ss” or “s”)
  • millisecond (“ms”)
  • microsecond (“mcs”)
  • nanosecond (“ns”)

The DATEDIFF () function also has a range constraint. The calculated date difference must be in the range of integers (-2,147,483,647 to 2,147,483,648).

Using DATEDIFF ()

In this section, we will look at different ways to use the function. For demonstration purposes, the MySQL server is configured with phpMyAdmin for ease of use. Check out this tutorial on how to set up phpMyAdmin on Ubuntu.

Primary use

On the SQL tab, run the following query.

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

As you can see from the output, the difference between the dates shown is 25568 days.

Compared to earlier date

What if the second date was later than the first? Swap the values ​​and check them.

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

As we can see, it still shows 25568 days. However, the value is negative. There is an important difference to keep in mind when implementing this functionality in any SQL script / query.

Date and time values

The DATEDIFF () function also takes datetime values ​​as a parameter. 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';

Please note that the additional time value does not affect the calculation result. The function focuses on the date only.

Dealing with Incorrect Date Values

If the date values ​​are incorrect, then DATEDIFF () will return NULL. For demonstration, enter an invalid date in any of the options.

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

As expected, the return value is NULL.

Combining DATEDIFF () with CURDATE ()

The CURDATE () function returns the current date of the car. Requires no parameters. Learn more about using CURDATE () to insert the current date and time into MySQL.

Using CURDATE (), we can find the difference between the current date and the scheduled date. For example, let’s compare the current date with 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 such as CURRENT_DATE () that act in the same way as CURDATE (). They can be swapped in situations.

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

Using DATEDIFF () with Tables

So far, we have implemented simple DATEDIFF () commands to demonstrate its use. It’s time to bring it to life.

We took a sample database containing various information about a specific company and its employees for demonstration. Sample database is available directly here… We will select the first and last names of employees from this database and find out how long they have worked so far.

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

Final thoughts

This article successfully demonstrates the use of the DATEDIFF () function in MySQL. It calculates the difference between two dates and returns the number of days. All demos help you understand the workflow of the DATEDIFF function.

Related Posts