I have a database structure like this
ID | title | startTime | endTime
And what I need is to get 3 records from the database where the first recorder is following this structure DAY(NOW())-1
the second record has to be like DAY(NOW())
or the first upcoming record and the third has to be like DAY(NOW())+1
What I was able to do is this:
SELECT DISTINCT title, FROM_UNIXTIME(startTime) as start FROM tl_calendar_events
WHERE MONTH(FROM_UNIXTIME(startTime)) = MONTH(NOW())
AND DAY(FROM_UNIXTIME(startTime)) >= DAY(NOW())
ORDER BY DAY(FROM_UNIXTIME(startTime)) ASC LIMIT 3;
But this will return the first 3 upcoming records. The startTime
and endTime
are all unix timestamp fields. So the final output of this query would look something like this if I take that the date is: 06-12-2015
:
- Record #1 (startTime - 10-11-2015)
- Record #2 (startTime - 06-12-2015)
- Record #3 (startTime - 12-01-2016)
Also I need some sort of a DISTINCT select so that if it happens I have like 3 records with the on the same day I only select one. I figured I could do this with 3 joins but just ended up creating a lot of mess. I hope you understood my question and can provide some help.
SAMPLE DATA:
- Event #1 (04-11-2015)
- Event #2 (06-12-2015)
- Event #3 (10-12-2015)
- Event #4 (12-03-2016)
- Event #5 (17-04-2016)
SAMPLE OUTPUT
EVENT #2 (as it is the first one before the current date)
EVENT #3 (as it is the current date or the first closest match)
EVENT #4 (as it is the first one to come after event #3)
I hope this will help explain my situation much better.