Difference Between Two Dates in MySQL

Difference between two dates in MySQL

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.

Difference between two dates in MySQL with DATE() function

MySQL doesn't support subtracting one date from another. The code

SELECT DATE '2020-07-15' - DATE '2020-06-29';

should hence result in an error, but MySQL silently converts this to this instead:

SELECT 20200715 - 20200629;

Seeing that you want to subtract two values, it assumes that you want to work with numbers. Dates are not numbers, but their internal representation yyyymmdd can be represented numerically. So, while CAST(DATE '2020-07-15 ' AS int) fails with a syntax error, as it should, MySQL is not consistent, when it comes to subtraction. It generates the numbers 20200715 and 20200629 and works with these.

I consider this a bug. MySQL should either raise an exception or return an INTERVAL when subtracting one DATE from another.

Trying to find differences of hours between two dates in MySQL

Try using this query:

Syntax for SQL SERVER

SELECT DATEDIFF(hour, a.departure_time, a.arrival_time) as HourDiff
FROM airlines a
WHERE DATEDIFF(hour, a.departure_time, a.arrival_time) >= 8

Syntax for MySQL

SELECT TIMESTAMPDIFF(hour, a.departure_time, a.arrival_time) as HourDiff
FROM airlines a
WHERE TIMESTAMPDIFF(hour, a.departure_time, a.arrival_time) >= 8

Exact difference between two dates in MYSQL

SELECT 
YEAR('1991-05-21') - YEAR('1997-07-20') - (DATE_FORMAT('1991-05-21', '%m%d') < DATE_FORMAT('1997-07-20', '%m%d')) 
+ '.' +
PERIOD_DIFF(DATE_FORMAT('1991-05-21', '%Y%m'), DATE_FORMAT('1997-07-20', '%Y%m'))
FROM tbl_dates

Output: 6.2

How to get difference between two dates in months using MySQL query?

The DATEDIFF function can give you the number of days between two dates. Which is more accurate, since... how do you define a month? (28, 29, 30, or 31 days?)

Get the number of days of difference between two dates in MySQL

SELECT * 
FROM users 
WHERE DATEDIFF(NOW(), str_to_date(`lastlogin`, '%d/%m/%Y')) > 30

Hope this helps.

Difference between two dates in MySQL and display in php

Yo can use this function i write call as dataDiff($firstDay,$lastDay)

function dataDiff($date1, $date2) {
    $dateDiff = 0;
    $nextday = $date1;
    while($nextday <= $date2) {
        $nextday = date("Y-m-d", strtotime("+1 day", strtotime($nextday)));
        $dateDiff++;
    }
    return $dateDiff;
}

MySQL - Getting age and numbers of days between two dates

Try this:

SELECT a.user_id, b.last_name, b.first_name, c.birth_date, 
       FLOOR(DATEDIFF(CURRENT_DATE(), c.birth_date) / 365) age, 
       DATEDIFF(b.left_date, b.join_date) workDays
FROM users a
INNER JOIN users_signup b ON a.user_id a = b.user_id
INNER JOIN users_personal c ON a.user_id a = c.user_id
WHERE b.join_date >= '2013-01-01' AND b.join_date < '2014-01-01'
GROUP BY a.user_id

---

Related Topics