Posted By

bradless on 02/19/10

Tagged

mysql
date

Versions (?)

Last Edited at 02/19/10 12:06pm

Statistics

Viewed 961 times

Favorited by 0 user(s)

Related snippets

calculate the difference between dates in one columns within the same table

/ Published in: MySQL

I use something similar to the example to get the difference between statuses in a status log table that I have. It comes in handy when determining the number of days between status changes. This works in MySQL 5.0.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML

SELECT DATEDIFF(
 (SELECT DATE(date_field) FROM table WHERE id = '1' AND status_id = '2' ORDER BY date_field DESC LIMIT 1),
 (SELECT DATE(date_field) FROM table WHERE id = '1' AND status_id = '1' ORDER BY date_field DESC LIMIT 1)
	) AS difference