Menu
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.
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.
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.
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')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With