MySQL INTERVAL Mins

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?

Answers:

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.

Method 1

Try:

$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)
• use NOW() for CURRENT_DATE just means 2010-08-04, not including the time
• use < to get entries older than that date.

Method 2

SELECT * FROM `table_name` WHERE CURTIME() >= (`colname` + INTERVAL 120 MINUTE)

Here, colname is the column where you added timestamp at the time when the record was created.

Method 3

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`

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0