Menu
How can I get the difference between two timestamps in days? Should I be using a datetime column for this?
mysql> 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> 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) 
961
Discussion: If you'd like to calculate the difference between the timestamps in seconds, multiply the decimal difference in days by the number of seconds in a day, which equals 24 * 60 * 60 = 86400 , or the product of the number of hours in a day, the number of minutes in an hour, and the number of seconds in a minute.
The result of subtracting one timestamp (TS2) from another (TS1) is a timestamp duration that specifies the number of years, months, days, hours, minutes, seconds, and fractions of a second between the two timestamps. then %SECOND(RESULT) = %SECOND(TS1) - %SECOND(TS2).
As stated above, the format of date and time in our table shall be yyyy:mm: dd hh:mm: ss which is implied by DATETIME2. The time is in a 24-hour format. Syntax: SELECT * FROM TABLE_NAME WHERE DATE_TIME_COLUMN BETWEEN 'STARTING_DATE_TIME' AND 'ENDING_DATE_TIME';
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 | +------+ 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).
28
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
