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
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.