Select different dates from mysql

Question

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:

1. Record #1 (startTime - 10-11-2015)
2. Record #2 (startTime - 06-12-2015)
3. 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.

Answer 1

Please use yyyy-mm-dd to avoid confusion (mm-dd-yyyy vs dd-mm-yyyy) and be consistent with MySQL.

Does this give you the first item you want?

SELECT *
    FROM tbl
    WHERE ts < CURRENT_DATE()
    ORDER BY ts DESC
    LIMIT 1;

If so, does this give you the other two:

SELECT *
    FROM tbl
    WHERE ts > $foo
    ORDER BY ts
    LIMIT 2;

given that $foo is the timestamp you got from the first query.

I assume your ts is of datatype TIMESTAMP, not INT. DATETIME would also do.

If you need to display dd-mm-yyyy, use DATE_FORMAT in the SELECT, but not for internal calculations.