Table of Contents for

MySQL in a Nutshell, 2nd Edition

This function returns the time difference between the two times given, but only for the interval being compared. The intervals accepted are the same as those accepted for TIMESTAMPADD(). This function is available as of version 5.0.0 of MySQL. Here is an example:

SELECT NOW( ) AS Today,
TIMESTAMPDIFF(DAY, NOW( ), LAST_DAY(NOW( )))
AS 'Days Remaining in Month';

+---------------------+-------------------------+
| Today               | Days Remaining in Month |
+---------------------+-------------------------+
| 2008-01-12 02:19:26 | 19                      |
+---------------------+-------------------------+

This SQL statement retrieves the current date and time and uses the LAST_DAY() function to determine the date of the last day of the month. Then the TIMESTAMPDIFF() function determines the difference between the day of the date now and the day of the date at the end of the month.