How to calculate difference in days between two dates in MySQL

Let’s say we have a MySQL table where one column (date or datetime type) is named creation-date and contains dates in one of the YYYY-MM-DD or YYYY-MM-DD HH:MM:SS formats from the past and we need to calculate the difference in days since that date untill current date for each row.

First let’s see a preview of the creation-date column:

mysql> SELECT creation-date FROM table_dates LIMIT 0,5;
**************
* activated       *
**************

*2007-06-06*
*2007-10-15*
*2007-10-17*
*2007-10-18*
*2007-10-19*
*************

so we see dates lik 06th of June, 15th of October and so on. Now, let’s calculate the difference between these dates and current date:

********
* intval   *
********
*    259  *
*    128  *
*    126  *
*    125  *
*    124  *
********

so the difference in days between 6th of June and present day is 259 days. Same with the others.

I used in the queries above two MySQL date functions: DATEDIFF() and CURDATE().

SELECT DATEDIFF(CURDATE(), wdoc) AS intval FROM table_dates

Share This Post

Post Comment