Brian Breeden Brian Breeden - 1 year ago 68
SQL Question

SQL Finding upcoming date and time

SQL statement:

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.

Answer Source

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:

  • Change (or find who can change) the server timezone
  • 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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download