iPhone Developer iPhone Developer - 2 months ago 9
MySQL Question

Get all the sessions 15 mins prior to start time

I'm trying to get all the session list who are going to start in 15 minutes. The problem is with client and server timezones and conversions. I've this code so far.

MySQL server is on a different machine and client (nodejs server) is running on another machine.

sql = 'SELECT * FROM sessions
WHERE FROM_UNIXTIME(?) BETWEEN SUBTIME(session.start, "0:15:0") AND session.start'

values = [Math.round(Date.now()/1000)] // convert milliseconds to seconds

Answer

I'm expecting something like this:

select s.*
from sessions s
where s.start >= now() and s.start <= date_add(now(), interval 15 minute);

One reason your query doesn't work is because between expects the first comparison value to be less than the second.

Comments