SQL Finding upcoming date and time

Question

SQL statement:

SELECT * FROM table 
WHERE eventdate >= DATE(NOW()) 
AND eventtime > TIME(NOW()) 
ORDER BY eventdate ASC, eventtime ASC;

The goal is to select the event that is coming up next. It works just fine so long as there are not two events on the same date. For example:

Event 1, 11/17/2016 7:00am
Event 2, 11/17/2016 2:00pm
Event 3, 11/18/2016 9:00am

I want to select event 2 since it is passed 7:00 am on the 17th. The statement above would still select event 1.

Please do not tag this question with databases that you are not using. I have removed those tags. Please add only the tag for the database you *are* using. — John Conde, Nov 17 '16 at 13:31
Not sure I understood what you are looking for, but you may add a LIMIT 2. — FDavidov, Nov 17 '16 at 13:33
I do not want to select event 1, since it has already happened. But the statement above is selecting it. — Brian Breeden, Nov 17 '16 at 13:33
Are the columns on the database of type DATE and TIME or have you stored the date in a VARCHAR or somethng equally as unhelpful — RiggsFolly, Nov 17 '16 at 13:34
What database manager / software are you using (mySQL, SSMS, Oracle, Access)? What does `SELECT NOW()` return? — Fritz, Nov 17 '16 at 13:40
There must be something else going on here because when I test that query on MYSQL it works just as you say you want it to. It selects only the 1 row containing Event2 — RiggsFolly, Nov 17 '16 at 13:48
@Fritz That is the problem. It is two hours behind. How do I set it to EST? — Brian Breeden, Nov 17 '16 at 13:49
@Fritz I am new to this, but I am currently using PHPmyAdmin — Brian Breeden, Nov 17 '16 at 14:02
The original query you gave is simply wrong, regardless of the 2 hour _gap_ you see. @user2693928 has the right solution. The question is just about where the 2 hour gap comes from. Is the server running in a different time zone than where you are? — user1429080, Nov 17 '16 at 14:05
Yes. I believe the time zone of the server is different than where I am. — Brian Breeden, Nov 17 '16 at 14:08

score 1 · Answer 1 · 2016-11-17T13:46:39.997

1

This should work:

SELECT * FROM table 
WHERE eventdate > DATE(NOW())
OR
    (eventdate = DATE(NOW()) AND eventtime > TIME(NOW()) )

ORDER BY eventdate ASC, eventtime ASC;

Basically you compare if date is in the future(tomorrow) or date is today and time is passed.

If your date is in the future you don't care about the time in this case.

Try it.

edited Nov 17 '16 at 13:46

answered Nov 17 '16 at 13:39

Looks like the problem is that the NOW() is two hours behind. Any idea on how to change the time to EST? – Brian Breeden Nov 17 '16 at 13:59
Here is the answer: http://stackoverflow.com/questions/13916747/set-mysql-database-timezone-to-gmt – Nov 17 '16 at 14:01

score 0 · Answer 2 · answered Nov 17 '16 at 13:41

0

I would need the value stored in eventdate and eventtime fields in your table in context to your example. because if you are checking time > TIME(NOW()), it should not pick this event if time is passed.

answered Nov 17 '16 at 13:41

Manish Sharma

1
2

I see the first time is set at 8:06 when the event is at 7:00 am. If the code is running between 7:00 and 8:06, it will take the first event of the day as well. The timezone doesn't seems to be an issue as SQL statement is using NOW() function which is the function executed at the server time. It all depends what actually is the exact time at the server and if there is any time difference between place of event and server's time. – Manish Sharma Nov 17 '16 at 14:36

score 0 · Accepted Answer · edited May 23 '17 at 11:59

0

As determined in the comments, the issue lies with the timezone currently used by the server where the database resides. Because of this, and that you are using PHPmyAdmin, I believe you do not have the authority to change the server timezone.

Your possible solutions are:

Use data that is already in MST (UTC-07) or whatever time zone the server is on
Use the current data and subtract 2 hours or convert the data to a time zone
Rewrite the query to use NOW() + 2 hours

edited May 23 '17 at 11:59

Community

1
1

answered Nov 17 '16 at 14:10

Fritz

624
1
7
14

Suggest you remove the "Change (or find who can change) the server timezone". That would just move the problem to some other user which is not in the same time zone.. Btw, this is why in most cases it makes sense to store UTC date/time in the database, not local date/time. Everyone agrees on the current UTC time... – user1429080 Nov 17 '16 at 15:23
Noted and updated. I was answering under the impression that this was a personal project when I shouldn't have made that assumption. – Fritz Nov 17 '16 at 15:35

SQL Finding upcoming date and time

3 Answers3