By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `stackoverflow` (
`boat_id` int(11) NOT NULL,
`dock_id` int(11) DEFAULT NULL,
`startingAt` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `stackoverflow` (`boat_id`, `dock_id`, `startingAt`)
VALUES
(1, 1, '2020-01-01 00:00:00'),
(1, 2, '2020-02-01 00:00:00'),
(1, 3, '2020-03-01 00:00:00'),
(2, 2, '2020-01-01 00:00:00'),
(2, NULL, '2020-03-01 00:00:00');
select s.*
from stackoverflow s
where s.startingAt = (select max(s2.startingAt)
from stackoverflow s2
where s2.boat_id = s.boat_id and
s2.startingAt <= '2020-02-15'
) and
s.dock_id = 2
| boat_id | dock_id | startingAt |
|---|---|---|
| 1 | 2 | 2020-02-01 00:00:00 |
| 2 | 2 | 2020-01-01 00:00:00 |
select s.*
from (select s.*,
lead(startingAt) over (partition by boat_id order by startingAt) as endingAt
from stackoverflow s
) s
where s.startingAt <= '2020-02-15' and
(s.endingAt > '2020-02-15' or s.endingAt is null) and
s.dock_id = 2
| boat_id | dock_id | startingAt | endingAt |
|---|---|---|---|
| 1 | 2 | 2020-02-01 00:00:00 | 2020-03-01 00:00:00 |
| 2 | 2 | 2020-01-01 00:00:00 | 2020-03-01 00:00:00 |