CODEWITHSUNDEEP
sql
Brian Breeden
Brian Breeden

Reputation: 101

SQL Finding upcoming date and time

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.

Upvotes: 0

Views: 1572

Answers (3)

Fritz
Fritz

Reputation: 624

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

Upvotes: 0

user2693928
user2693928

Reputation:

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.

Upvotes: 1

Manish Sharma
Manish Sharma

Reputation: 1

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.

Upvotes: 0

Related Questions