CALL 1.800.975.6814    LIVE CHAT

@dailyrazor Blog

MySQL DateDiff – Quick Guide Seconds, Minutes, Hours, Months, Years

MySQL DateDiff

So working with dates is something every MySQL database developer must do once in a while. And most of the time, this will require calculating the number of days between date values. Fortunately, MySQL provides a handy date function to make such calculations easy-breezy – say hello to the MySQL datediff function.

Therefore, in this article, we’re going to provide you with information on how to use the datediff() function. In addition, we will show you practical situations where you’ll find this function most helpful in your MySQL development journey.

Let’s go!

ALSO READ: MySQL_Connect Function: The Complete Guide to MySQL_Connect()

 

[SPECIAL OFFER]: Fastest Web Hosting with FREE MySQL

[BENEFITS]:

  • FREE 1-Click Install of Open Source Apps, Blog, CMS, and much more!
  • Support for PHP, MySQL, ASP.NET, SQL Server, WordPress, Joomla and much more!
  • Multi-Domain Hosting & 99.9% Uptime Guarantee!
  • Super Fast Servers with 24/7/365 Technical Support!

Click here to access this [SPECIAL OFFER]

 

What Is MySQL DateDiff Function

In the first place, let’s put things into perspective – what is MySQL datediff() function? This function allows you to determine the difference between two dates quickly. Therefore, all you have to do is to pass required input parameters to the function; and it will return a value indicating the difference between the two inputs.

Syntax

The MySQL datediff function’s syntax is pretty simple:

datediff(expression1,expression2);

Note: expression1is the first date, and expression2 is the second date.

How To Find DateDiff In MySQL

When working with MySQL database and trying to figure out how to find datediff in MySQL, all you have to do is use the MySQL datediff function.

For example, let’s you have two dates: September 1, 2023, and September 20, 2023. And you want to programmatically find the difference between the two (without using your calculator or fingers to count), you’ll use the datediff() function as follows:

Select datediff('2023-09-20','2023-09-01') as Difference;

Once you run the above query against the database, you’ll get the following result:


+------------+
| Difference |
+------------+
|     19     |
+------------+

 

Notably, bear in mind that the MySQL datediff() function works with the following MySQL versions: 5.7, 5.6, 5.5, 5.1, 5.0 and 4.1.1.

What Does DateDiff Return MySQL

Equally important, you may be wondering what does the MySQL datediff function return. As you can see from the above example, the function will return the number of days between the two dates you provided.

Also, if you provided datetimes instead of date days, then the function will equally return the difference between the two provided date times in days.

 

[SPECIAL OFFER]: Fastest Web Hosting with FREE MySQL

[BENEFITS]:

  • FREE 1-Click Install of Open Source Apps, Blog, CMS, and much more!
  • Support for PHP, MySQL, ASP.NET, SQL Server, WordPress, Joomla and much more!
  • Multi-Domain Hosting & 99.9% Uptime Guarantee!
  • Super Fast Servers with 24/7/365 Technical Support!

Click here to access this [SPECIAL OFFER]

 

How To Use DateDiff In MySQL

Now, let’s take a look at how to use datediff() in MySQL with some practical examples so you can fully understand the concept.

MySQL DateDiff Seconds

First, how do you get the difference between two times in seconds? The datediff() function is probably not the best function to use in such a situation.

So if you’re trying to do MySQL datediff seconds, you’ll come to realize that the datediff() function only returns day values. Therefore, if you supply the seconds in the date, only the date portion is used in the calculation to return the number of days.

Now, there’s the timestampdiff() function which will provide you with the needed capability to calculate the differences between two datetime expressions. And the two expressions must be of the same type.

The syntax is as follows:

timestampdiff(unit,expression1,expression2);

And for the arguments:

1. unit: The unit can be one of the following options: FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.
2. expression1: This is a datetime expression you’ll substract expression2 from.
3. expression2: This is a datetime expression you’ll substract from expression1.

Therefore, if you’re looking for MySQL DateDiff seconds, you can use the TimeStampDiff function to accomplish your objective. For example:

Select timestampdiff(SECONDS, '2023-09-20 12:30:15', '2023-09-01 10:15:00') as Difference;

The above query statement will return the different between the two datetime values provided in seconds 2023-09-20 12:30:15 and 2023-09-01 10:15:00 as shown below:


+------------------+
|    Difference    |
+------------------+
|         15       |
+------------------+

 

MySQL DateDiff Minutes

Likewise, there will be times you need to get the difference in two datetime expression in minutes. Consequently, your natural inclination might be to there’s a MySQL DateDiff Minutes as a solution.

However, as stated above, you’ll come to realize that this won’t work out very well. Instead, use the TimeStampDiff() function to accomplish this task.

Select timestampdiff(MINUTES, '2023-09-20 12:30:15', '2023-09-01 10:15:00') as Difference;

The above query statement will return the difference between the two datetime values provided in minutes 2023-09-20 12:30:15 and 2023-09-01 10:15:00 as shown below:


+------------------+
|    Difference    |
+------------------+
|         15       |
+------------------+

 

MySQL DateDiff Hours

Furthermore, as you can see from the above two examples when looking for the difference in two datetime expressions, the MySQL DateDiff hours is not a reliable solution.

Once again, you can use the TimeStampDiff() function to get the job done. Let’s take look:

Select timestampdiff(HOURS, '2023-09-20 12:30:15', '2023-09-01 10:15:00') as Difference;

The above query statement will return the difference between the two datetime values provided in hours 2023-09-20 12:30:15 and 2023-09-01 10:15:00 as shown below:


+------------------+
|    Difference    |
+------------------+
|        2         |
+------------------+

 

MySQL DateDiff Days

The MySQL DateDiff() function’s default return value is the number of days between two date or datetime expressions.

And as we’ve also indicated earlier, if you run a query statement like this:

Select datediff('2023-09-20','2023-09-15') as Difference;

You’ll get the following result:


+------------+
| Difference |
+------------+
|     5      |
+------------+

 

The result tells you that there’s a difference of 5 days between the two expressions. So this is how the function works.

 

[SPECIAL OFFER]: Fastest Web Hosting with FREE MySQL

[BENEFITS]:

  • FREE 1-Click Install of Open Source Apps, Blog, CMS, and much more!
  • Support for PHP, MySQL, ASP.NET, SQL Server, WordPress, Joomla and much more!
  • Multi-Domain Hosting & 99.9% Uptime Guarantee!
  • Super Fast Servers with 24/7/365 Technical Support!

Click here to access this [SPECIAL OFFER]

 

MySQL DateDiff Months

Of course, there will be occasions where you need to know the difference between two periods. Particularly, let’say you want to know the difference between the periods in months.

Certainly, trying to use the MySQL DateDiff Months in such a situation won’t cut. Luckily, MySQL provides another handy function called the PERIOD_DIFF() which can do the trick.

Syntax:

The PERIOD_DIFF() syntax is as follows:

period_diff(expression1,expression2);

where “expression1” indicates the first period and “expression2” means the second period to subtract from “expression1”.

So here’s an example:

Select period_diff(202310,202302) As Difference;

The above query statement will output


+------------------+
|    Difference    |
+------------------+
|        8         |
+------------------+

 

The result shows that there is eight months difference between the two periods.

MySQL DateDiff Years

Moreover, if you’re looking for MySQL DateDiff Years, you can accomplish the same using the PERIOD_DIFF() function above as well as the TIMESTAMPDIFF() described above as well.

For PERIOD_DIFF() example:

Select period_diff(2025,2023) As Difference;

The above query statement will return:


+------------------+
|    Difference    |
+------------------+
|        2         |
+------------------+

 

For TIMESTAMPDIFF() example:

Select timestampdiff(YEAR, '2025-09-20 12:30:15', '2023-09-01 10:15:00') as Difference;

The above query statement will also return:


+------------------+
|    Difference    |
+------------------+
|        2         |
+------------------+

 

MySQL DateDiff Current Date

Besides, you can use the function together with the Current Date function. The CURDATE() function returns the value of the current date on the system.

So here’s a practical example:

Select datediff(curdate(),'2023-09-01') as Difference;

Once you run the above query against the database, If the current date is ‘2023-09-30’, you’ll get the following result (in days):


+------------+
| Difference |
+------------+
|     29     |
+------------+

 

Final Thought

We hope that this article has given you more insight into this function, it’s applications as well as limitations. As with anything, feel free to solidify your understanding of the fundamental concepts by reading other available resources as well as MySQL documentation. Good luck!

 

[SPECIAL OFFER]: Fastest Web Hosting with FREE MySQL

[BENEFITS]:

  • FREE 1-Click Install of Open Source Apps, Blog, CMS, and much more!
  • Support for PHP, MySQL, ASP.NET, SQL Server, WordPress, Joomla and much more!
  • Multi-Domain Hosting & 99.9% Uptime Guarantee!
  • Super Fast Servers with 24/7/365 Technical Support!

Click here to access this [SPECIAL OFFER]