MySql difference between two timestamps in days

Question

How can I get the difference between two timestamps in days  Should I be using a datetime column for this   I switched my column to datetime  Simple subtraction doesn t seem to give me a result in days    mysql gt  SELECT NOW    last confirmation attempt  NOW   - last confirmation attempt AS diff  FROM DateClubs HAVING diff IS NOT NULL    --------------------- --------------------------- -----------------    NOW                   last confirmation attempt   diff               --------------------- --------------------------- -----------------    2010-03-30 10 52 31   2010-03-16 10 41 47         14001084 000000    --------------------- --------------------------- -----------------  1 row in set  0 00 sec    I don t think diff is in seconds  because when I divide diff by number of seconds in a day   86 400    I don t get a sensical answer   mysql gt  SELECT NOW    last confirmation attempt    NOW   - last confirmation attempt    86400 AS diff  FROM DateClubs HAVING diff IS NOT NULL    --------------------- --------------------------- ----------------    NOW                   last confirmation attempt   diff              --------------------- --------------------------- ----------------    2010-03-30 10 58 58   2010-03-16 10 41 47         162 0568402778    --------------------- --------------------------- ----------------  1 row in set  0 00 sec

User · Accepted Answer

If you re happy to ignore the time portion in the columns  DATEDIFF   will give you the difference you re looking for in days   SELECT DATEDIFF  2010-10-08 18 23 13    2010-09-21 21 40 36   AS days   ------    days    ------      17    ------

User · Answer

I know is quite old  but I ll say just for the sake of it - I was looking for the same problem and got here  but I needed the difference in days   I used SELECT  UNIX TIMESTAMP DATE1  - UNIX TIMESTAMP DATE2   60 60 24 Unix timestamp returns the difference in seconds  and then I just divide into minutes seconds 60   hours minutes 60   days hours 24

User · Answer

If you want to return in full TIMESTAMP format than try it  -    SELECT TIMEDIFF  call end time    call start time   as diff from tablename    return like        diff      - - -     00 05 15

User · Answer

CREATE TABLE t  d1 timestamp  d2 timestamp    INSERT INTO t VALUES   2010-03-11 12 00 00    2010-03-30 05 00 00    INSERT INTO t VALUES   2010-03-11 12 00 00    2010-03-30 13 00 00    INSERT INTO t VALUES   2010-03-11 00 00 00    2010-03-30 13 00 00    INSERT INTO t VALUES   2010-03-10 12 00 00    2010-03-30 13 00 00    INSERT INTO t VALUES   2010-03-10 12 00 00    2010-04-01 13 00 00     SELECT d2  d1  DATEDIFF d2  d1  AS diff FROM t    --------------------- --------------------- ------    d2                    d1                    diff    --------------------- --------------------- ------    2010-03-30 05 00 00   2010-03-11 12 00 00     19     2010-03-30 13 00 00   2010-03-11 12 00 00     19     2010-03-30 13 00 00   2010-03-11 00 00 00     19     2010-03-30 13 00 00   2010-03-10 12 00 00     20     2010-04-01 13 00 00   2010-03-10 12 00 00     22    --------------------- --------------------- ------  5 rows in set  0 00 sec

User · Answer

SELECT DATEDIFF max date  min date  as days from my table  This works even if the col max date and min date are in string data types

User · Answer

SELECT DATEDIFF  now     2013-06-20      here datediff takes two arguments  upto-date    from-date  What i have done is  using now   function  i can get no  of days since 20-june-2013 till today

User · Answer

If you need the difference in days accounting up to the second   SELECT TIMESTAMPDIFF SECOND  2010-09-21 21 40 36   2010-10-08 18 23 13   86400 AS diff   It will return     diff     16 8629

[mysql] MySql difference between two timestamps in days?

Examples related to mysql

Examples related to datetime

Examples related to timestamp