Calculating difference between two mysql dates in seconds
Here is a quick way to calculate the difference between two mysql dates in number of seconds. In my case, i required to fetch all entries for which the difference between current time stamp and date in a table field would be less than 5 minutes.
SELECT TIME_TO_SEC(TIMEDIFF(now(), `last_active`)) as timediff FROM `users` where TIME_TO_SEC(TIMEDIFF(now(), `last_active`)) < 300
In the above example i do calculate difference between current time (now()) and date stored in the "last_active" field of my "users" table. TIMEDIFF function of MySQL calculates this difference in "hh:mm:ss" format and TIME_TO_SEC function of MySQL converts this difference into number of seconds.
This is how i did it. If someone knows of a better way to do it, please suggest. Thanks.
Possibly Related posts:
- To find mysql recodrs between specific date range based on start date and end date fields
While working on a report generation system i had to perform a query to check whether records exist in ‘reports’ table between a date range... - A few notes on joining a MySQL table to itself to get unique combination of field data
Just a few notes on joining a MySQL table to itself to get unique combination of data stored in its fields. Suppose we have a... - Fixing ‘mysql’ is not recognized as an internal or external command in Windows7
Having WAMP installed when i try to type mysql in my windows command prompt it would give me the following error: 'mysql' is not recognized... - A note on difference between app:import and loadmodel in CakePHP
Posting for, someone searched for terms “cakephp what is the difference between app:import model and loadmodel” and “cakephp what is the difference between app:import and... - Full-text search in MySQL
Normally, most of us use “SELECT * FROM table WHERE field1 LIKE ‘%$keyword%’” OR field2 LIKE ‘%$keyword%‘ ..etc” to search our table and get results....
If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.
