How does date_add and interval works in MySQL?

Question

I try to select those rows from the table in which datatime is withing 1 minute from now. I try to do it in the following way:

SELECT datetime FROM my_table 
WHERE datetime > date_add( curdate( ) , INTERVAL -1 MINUTE )

This is what I get:

2011-09-23 02:39:53
2011-09-23 08:10:07

Something is clearly wrong with that. Does anybody know why?

ADDED

It looks really strange. If I use interval -1 hour I got results which are 4 or 7 hours old (I should not get them) but I do filter results which are older than 14 hours. How is it possible?

Use `now()` instead of `curdate()` – Johan Sep 23 '11 at 11:59 — Johan, Sep 23 '11 at 11:59

Marco · Accepted Answer · 2011-09-23T11:43:05.387

5

From one minute in the past to one minute in the future:

SELECT * FROM my_table
WHERE datetime BETWEEN
    DATE_ADD(NOW(), INTERVAL -1 MINUTE) AND
    DATE_ADD(NOW(), INTERVAL 1 MINUTE)

From now to one minute in the future:

SELECT * FROM my_table
WHERE datetime BETWEEN
    NOW() AND
    DATE_ADD(NOW(), INTERVAL 1 MINUTE)

From one minute in the past to now:

SELECT * FROM my_table
WHERE datetime BETWEEN
    DATE_ADD(NOW(), INTERVAL -1 MINUTE) AND
    NOW()

edited Sep 23 '11 at 11:43

answered Sep 23 '11 at 11:35

Marco

56,740
14
129
152

It looks to me that the first query should return results which are within 1 minute to the past and to the FUTURE from now. The second query should return results which are within 1 minute from now into the future. – Roman Sep 23 '11 at 11:40
@Roman: edited my post to cover all cases... I suspect I didn't understand your question... – Marco Sep 23 '11 at 11:43
I do not need results from the future. I need those which are within one minute interval from now into the past. So, may be it should be `select * from my_table where datetime between date_add(now(), interval -1 minute) and now()`. – Roman Sep 23 '11 at 11:44
May be I need to use `date_sub`? But anyway, I tried it and it did not work. – Roman Sep 23 '11 at 11:44
@Roman: I tried my third query right now and it works well.. I don't know what's happening in your db... – Marco Sep 23 '11 at 11:49
@Roman: are you sure that your mysql server date and time are set correctly? It could be thi the reason of your failures... – Marco Sep 23 '11 at 11:50
Actually it works the way you suggested. Thank you very much! – Roman Sep 23 '11 at 12:01
Thank you so much, this is so helpful. What if I want to have `Hours` instead of `Minutes` ? Is there a way? or conversion is the only way? – Sobiaholic Jul 25 '13 at 11:08
@iMohammad: yes, change `MINUTE` with `HOUR` in the query!! Check syntax [here](http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-add) – Marco Jul 25 '13 at 11:12
Oh boy! I used `HOURS` instead of `HOUR` that's why it gave me an error. Thanks @Marco ! – Sobiaholic Jul 25 '13 at 11:44

How does date_add and interval works in MySQL?

1 Answers1