i want to get data from table, and example of data like this:

value of example is "2022-08-07 00:00:00", what i want is get data "Event 1" as the latest event because there is no event starting in "2022-08-07".

and when the value of example "2022-08-08 12:12:12", what i want is get data "Event 2" because there is a event starting from that date. And when the value is "2022-08-09 08:00:00" i want to still get the data "Event 2", because the date is still lower than end date.

How to query in MySQL, so i can result like that?

CodePudding user response：

Assuming proper column names, it would be as below for your initial query (swap out the date for each of your examples):

SELECT *
FROM table
WHERE StartDate <= "2022-08-07 00:00:00"
ORDER BY StartDate desc
LIMIT 1;

Of course if you're basing it of when you run the SQL:

SELECT *
FROM table
WHERE StartDate <= now()
ORDER BY StartDate desc
LIMIT 1;

CodePudding user response：

My approach would be the following nested SELECTs - replace the mentioned timestamp '2022-08-16 12:12:12' with NOW() or any timestamp you need in your case

SELECT * FROM
(
  (
    SELECT a.*,-1 AS mynumber
    FROM `testtest2` AS a
    WHERE start<='2022-08-16 12:12:12' AND end >='2022-08-16 12:12:12'
  )
  UNION
  (
    SELECT a.*,b.mynumber
    FROM `testtest2` AS a,
    (SELECT COUNT(*) AS mynumber FROM `testtest2` WHERE start<='2022-08-16 12:12:12' AND end >='2022-08-16 12:12:12') AS b
    WHERE end<='2022-08-16 12:12:12' ORDER BY end DESC LIMIT 1
  )
) c 
WHERE mynumber<=0

Explanation

One SELECT retrieves all events currently active (start <= timestamp AND end >= timestamp) and sets the column mynumber to the fixed value -1 (which clearly is < 0).

The next SELECT retrieves events that have already passed, sorted descending by the column end and only retrieve the first (= newest/last) event. The column mynumber is filled with the numer of events that are currently running - so this column contains either a 0 (if there are no events currently running) or a positive number (> 0) if there are other events running.

These two SELECTs are combined using UNION and used as source for the outer SELECT which only retrieves events with a value <=0 for the column mynumber.

This way you get either the active events OR (in case there are no active events) the last/newest event which has already passed.