How to limit time when SELECTing (MYSQL)

Question

I have a table in database and a column named timedate the time is stored like this in this column: 2013-05-25 12:15:25

I need to SELECT the row with the time within last 15 minutes only; so in the case above

SELECT if timedate is after 2013-05-25 12:00:25

I tried:

TO_DAYS(NOW()) - TO_DAYS('timedate') < 15

but it didn't work

SELECT 
    * 
  FROM 
    `column` 
  WHERE 
    `name` = 'name' 
    AND `family` = 'family'  
    AND (
      `test1` = 'test1 value' 
      || `test2` = 'test2 value' 
      || `test3` = 'test3 value' 
    ) 
    AND TO_DAYS(NOW()) - TO_DAYS('timedate') < 15 
  LIMIT 
    1

MySQL TO_DAYS() returns number of days between a given date and year 0 — srakl, Jul 21 '13 at 12:22
You might be interested in reading: [When to use single quotes, double quotes, and backticks?](http://stackoverflow.com/q/11321491/367456) — hakre, Jul 21 '13 at 12:53
There is no `E_DOESNT_WORK` in PHP. You have to explain by what criteria you determined that it "doesn't work". — tereško, Aug 02 '13 at 20:59

score 1 · Accepted Answer · answered Jul 21 '13 at 12:29

SELECT * FROM `column` 
WHERE `name` = {$name} 
AND `family` = {$family}  
AND (`test1` = {$test1} || `test2` = {$test2} || `test3` = {$test3}) 
AND `timedate` > NOW() - INTERVAL 15 MINUTE

Important: Note that I replaced your ' around timedate with backticks. With ' MySQL thinks it's a string, not a column name.

score 1 · Answer 2 · answered Jul 21 '13 at 12:45

1

If type of field test1, test2 and test3 is number, then your query should be :

SELECT 
  * 
FROM
  `column` 
WHERE `name` = '{$name}' 
  AND `family` = '{$family}'
  AND (
    `test1` = {$test1} || `test2` = {$test2} || `test3` = {$test3}
  ) 
  AND ADDTIME(`timedate`, '00:15:00.000000') > NOW()

And if type of field test1, test2 and test3 is varchar, your query should be :

SELECT 
  * 
FROM
  `column` 
WHERE `name` = '{$name}' 
  AND `family` = '{$family}'
  AND (
    `test1` = '{$test1}' || `test2` = '{$test2}' || `test3` = '{$test3}'
  ) 
  AND ADDTIME(`timedate`, '00:15:00.000000') > NOW()

Hopefully this help.

answered Jul 21 '13 at 12:45

Erdo Dirgagautama

461
3
9

thanks but why number or varchar for type of field? the type is datetime – Vladimir Jul 21 '13 at 12:47
1

Sorry, what I mean is field test1, test2 and test3. I update the answer. Also please enclose {$name} and {$family} with '. :) – Erdo Dirgagautama Jul 21 '13 at 12:48

score 0 · Answer 3 · answered Jul 21 '13 at 12:24

0

See this page:

You can use DATE_SUB(CURDATE(), INTERVAL 15:00 MINUTE_SECOND) to get the time 15 minutes ago, and then compare the dates with the > operator.

answered Jul 21 '13 at 12:24

pascalhein

5,530
4
30
41

score 0 · Answer 4 · answered Jul 21 '13 at 12:25

0

Try TIMESTAMPDIFF (MINUTE, NOW(), timestamp)

See the MySQL manual

answered Jul 21 '13 at 12:25

nurdglaw

2,031
17
33

score 0 · Answer 5 · answered Jul 21 '13 at 12:26

0

try something like this

SELECT TIMEDIFF(NOW(),`timedate`) < 15 FROM ......

answered Jul 21 '13 at 12:26

srakl

2,475
2
19
32

i don't think this will work, as the output is like 46:58:57.999999 it will work for hours not minutes – Vladimir Jul 21 '13 at 12:44

How to limit time when SELECTing (MYSQL)

5 Answers5