Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Difference between two dates in MySQL

People also ask

Can we subtract two dates in MySQL?

DATE_SUB() function in MySQL is used to subtract a specified time or date interval to a specified date and then returns the date. Parameter: This function accepts two parameters which are illustrated below : date – Specified date to be modified. value addunit – Here the value is date or time interval to subtract.

How do I find the difference between two dates in SQL query?

To find the difference between dates, use the DATEDIFF(datepart, startdate, enddate) function. The datepart argument defines the part of the date/datetime in which you'd like to express the difference. Its value can be year , quarter , month , day , minute , etc.

How can I get the difference between two dates in a month in MySQL?

PERIOD_DIFF() function MySQL PERIOD_DIFF() returns the difference between two periods. Periods should be in the same format i.e. YYYYMM or YYMM. It is to be noted that periods are not date values.

How can we know the number of days between two given dates using MySQL?

Use the DATEDIFF() function to retrieve the number of days between two dates in a MySQL database. This function takes two arguments: The end date. (In our example, it's the expiration_date column.)


SELECT TIMEDIFF('2007-12-31 10:02:00','2007-12-30 12:01:01');
-- result: 22:00:59, the difference in HH:MM:SS format


SELECT TIMESTAMPDIFF(SECOND,'2007-12-30 12:01:01','2007-12-31 10:02:00'); 
-- result: 79259  the difference in seconds

So, you can use TIMESTAMPDIFF for your purpose.


If you are working with DATE columns (or can cast them as date columns), try DATEDIFF() and then multiply by 24 hours, 60 min, 60 secs (since DATEDIFF returns diff in days). From MySQL:

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

for example:

mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30 00:00:00') * 24*60*60

Get the date difference in days using DATEDIFF

SELECT DATEDIFF('2010-10-08 18:23:13', '2010-09-21 21:40:36') AS days;
+------+
| days |
+------+
|   17 |
+------+

OR

Refer the below link MySql difference between two timestamps in days?


SELECT TIMESTAMPDIFF(HOUR,NOW(),'2013-05-15 10:23:23')
   calculates difference in hour.(for days--> you have to define day replacing hour
SELECT DATEDIFF('2012-2-2','2012-2-1')

SELECT TO_DAYS ('2012-2-2')-TO_DAYS('2012-2-1')