0

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.

  • You probably need GROUP BY – Mihai Dec 6 '15 at 18:58
  • This question isn't very clear as it stands, can you add some more info like sample data and expected output from that sample data? – Tom V Dec 7 '15 at 6:50
  • @TomV I have updated the description with the sample data and sample output of what my result should look like. As this is a table in the db that holds calendar events – Igor Ilic Dec 7 '15 at 11:12
  • If events 3 and 4 were the same date (but different time) would you still want events 2, 3 and 4 in the result? Or it should be then 2, 3 and 5 (with different date than 3)? – ypercubeᵀᴹ Dec 7 '15 at 11:18
  • @ypercube I only want one event per day so if there are more events with the same day just select one. :) – Igor Ilic Dec 7 '15 at 13:07
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.