206

Which is the valid syntax of this query in MySQL?

SELECT * FROM courses WHERE (now() + 2 hours) > start_time

note: start_time is a field of courses table

Ei Maung
  • 7,013
  • 5
  • 23
  • 23

5 Answers5

389
SELECT * 
FROM courses 
WHERE DATE_ADD(NOW(), INTERVAL 2 HOUR) > start_time

See Date and Time Functions for other date/time manipulation.

Glavić
  • 42,781
  • 13
  • 77
  • 107
52

You need DATE_SUB() OR DATE_ADD()

Sergey Galashyn
  • 6,946
  • 2
  • 19
  • 39
34
SELECT * FROM courses WHERE (NOW() + INTERVAL 2 HOUR) > start_time
iblue
  • 29,609
  • 19
  • 89
  • 128
Daniël van Eeden
  • 603
  • 5
  • 7
29

The DATE_ADD() function will do the trick. (You can also use the ADDTIME() function if you're running at least v4.1.1.)

For your query, this would be:

SELECT * 
FROM courses 
WHERE DATE_ADD(now(), INTERVAL 2 HOUR) > start_time

Or,

SELECT * 
FROM courses 
WHERE ADDTIME(now(), '02:00:00') > start_time
lc.
  • 113,939
  • 20
  • 158
  • 187
  • If only there were examples. – Tomas Jun 04 '14 at 07:13
  • The documentation has some good examples (and SO is not meant to be a replacement for the documentation).. But in the interest of this question and future visitors, I have added both syntaxes. – lc. Jun 04 '14 at 07:39
1

This will also work

SELECT NAME 
FROM GEO_LOCATION
WHERE MODIFY_ON BETWEEN SYSDATE() - INTERVAL 2 HOUR AND SYSDATE()
Tony
  • 9,672
  • 3
  • 47
  • 75
Soumyajit Swain
  • 1,298
  • 1
  • 21
  • 35