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 activated 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 activated column:

mysql> SELECT activated FROM table_dates LIMIT 0,5; +--------------+ | activated     | +--------------+ | 2007-06-06 | | 2007-10-15 | | 2007-10-17 | | 2007-10-18 | | 2007-10-19 | +--------------+ 5 rows in set (0.00 sec) mysql>

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:

mysql> SELECT DATEDIFF(CURDATE(), activated) AS intval FROM table_dates LIMIT 0,5; +--------+ | intval   | +--------+ |    259  | |    128  | |    126  | |    125  | |    124  | +--------+ 5 rows in set (0.00 sec)

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().

Also take a good look at MySQL Date and Time Functions and MySQL The DATETIME, DATE, and TIMESTAMP Column Types.

Bookmarks:

Echo "How to calculate difference in days between two dates in MySQL" around:

-------------------advertising-----------------