I am trying to get the all records which are 2 hours or more old using this query:
$minutes = 60 * 2 SELECT COUNT(id) AS TOTAL, job_id from tlb_stats WHERE log_time >= DATE_SUB(CURRENT_DATE, INTERVAL $minutes MINUTE) GROUP BY job_id
It only selects the recent records and skips the old. When I change
log_time <= ... it only selects old and skips which are the new one.
What am I doing wrong?
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
$minutes = 60 * 2 SELECT COUNT(`id`) AS `TOTAL`, `job_id` FROM `tlb_stats` WHERE `log_time` < DATE_SUB(NOW(), INTERVAL $minutes MINUTE) GROUP BY `job_id`
- use backticks to quote fields (words like “total” and “id” may someday mean something in MySQL)
NOW()for CURRENT_DATE just means 2010-08-04, not including the time
<to get entries older than that date.
SELECT * FROM `table_name` WHERE CURTIME() >= (`colname` + INTERVAL 120 MINUTE)
colname is the column where you added timestamp at the time when the record was created.
You can also do it in this way:
$minutes = 60 * 2 SELECT COUNT(`id`) AS `TOTAL`, `job_id` FROM `tlb_stats` WHERE `log_time` < NOW() - INTERVAL $minutes MINUTE GROUP BY `job_id`