MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

DROP TABLE IF EXISTS `table`;
    CREATE TABLE `table` (id INT, name VARCHAR(255), `date` DATETIME);
    INSERT INTO `table` (id, name, `date`) VALUES (10, 'a', '2020-01-08 20:40:00');
    INSERT INTO `table` (id, name, `date`) VALUES (9, 'b', '2020-01-08 20:40:00');
    INSERT INTO `table` (id, name, `date`) VALUES (8, 'c', '2020-01-08 20:40:00');
    INSERT INTO `table` (id, name, `date`) VALUES (500, 'd', '2020-01-06 22:49:00');
    INSERT INTO `table` (id, name, `date`) VALUES (7, 'e', '2020-01-06 22:00:00');

SELECT id, name, `date` FROM `table` ORDER BY `date` DESC, id DESC;

SET @r_id = 8;
    SELECT @r_id;

-- Previous ID
    WITH cte_desc AS (SELECT * FROM `table` ORDER BY `date` DESC, id DESC),
         cte_r AS (SELECT * FROM `table` WHERE id = @r_id)
    SELECT id AS prev_id
    FROM cte_desc
    WHERE `date` < (SELECT `date` FROM cte_r)
       OR `date` = (SELECT `date` FROM cte_r) AND id < (SELECT id FROM cte_r)
    LIMIT 1;

-- Next ID
    WITH cte_asc AS (SELECT * FROM `table` ORDER BY `date`, id),
         cte_r AS (SELECT * FROM `table` WHERE id = @r_id)
    SELECT id AS next_id
    FROM cte_asc
    WHERE `date` > (SELECT `date` FROM cte_r)
       OR `date` = (SELECT `date` FROM cte_r) AND id > (SELECT id FROM cte_r)
    LIMIT 1;

id	name	date
10	a	2020-01-08 20:40:00
9	b	2020-01-08 20:40:00
8	c	2020-01-08 20:40:00
500	d	2020-01-06 22:49:00
7	e	2020-01-06 22:00:00

@r_id
8

prev_id
500

next_id
9