MySQL INTERVAL Mins

Question

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?

You are doing all right - that is what this query should do. — zerkms, Aug 03 '10 at 23:43
@jason4: because you want the records with a time _less or equal_ (`<=`) to the current time minus 2 minutes, (i.e. now: 22:06, 2 mins ago is 22:04, so you want a record with 22:03, which is less then 22:04). — Wrikken, Aug 04 '10 at 00:05

score 25 · Accepted Answer · edited Apr 24 '19 at 22:02

25

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.

edited Apr 24 '19 at 22:02

simhumileco

31,877
16
137
115

answered Aug 04 '10 at 00:09

mvds

45,755
8
102
111

score 1 · Answer 2 · edited Apr 24 '19 at 22:03

1

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.

edited Apr 24 '19 at 22:03

simhumileco

31,877
16
137
115

answered Mar 07 '15 at 10:20

DeathRs

1,100
17
22

4

Recommend rearranging the expression to `WHERE colname <= ...` so that `INDEX(... colname)` can be used. – Rick James Sep 05 '17 at 22:48

score 0 · Answer 3 · answered Apr 24 '19 at 22:18

0

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`

answered Apr 24 '19 at 22:18

simhumileco

31,877
16
137
115

MySQL INTERVAL Mins

3 Answers3

Linked