Friday, 27 July 2007

MySQL time date difference in Minutes

Here's a handy MySQL function from finding the number of minutes since a given date. In the following example, there is a table called message with 3 colums - id, pubdate and message;

SELECT TIMESTAMPDIFF(MINUTE, pubdate, now()), id, message from message;

Messages from the last 5 minutes


You can get all messages from the last 5 minutes with the following example;

SELECT TIMESTAMPDIFF(MINUTE, pubdate, now()), id, message from message where (TIMESTAMPDIFF(MINUTE, pubdate, now()) < 5);

This method is quite handy for systems displaying live data.

6 comments:

Anonymous said...

thany you!

Anonymous said...

The query helped..

Alberto said...

help me to solve a bug.. thanks a lot!

Zoran said...

Hey, thanks a lot for this

karser said...

thank you! good method

I n u said...

Thanks, finally I got here...