Brian Breeden Brian Breeden - 16 days ago 6
SQL Question

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.

Answer

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
Comments