How to find the most appropriate date between overlapping date periods in mysql

I`w got an 2 tables with such data:

Table 1

id	s_id	s_date
1	33	2021-03-05 10:36:59
2	33	2021-03-06 10:36:59
3	33	2021-03-09 10:36:59
4	33	2021-03-10 13:36:59
5	33	2021-03-11 12:36:59
6	33	2021-03-12 09:00:59
7	33	2021-03-13 13:36:59
8	33	2021-03-14 18:00:00
9	33	2021-03-15 18:00:00
10	33	2021-03-16 13:00:00
11	33	2021-03-17 18:00:00
12	33	2021-03-18 14:00:00
13	33	2021-04-01 18:00:00
14	33	2021-05-02 14:00:00

Table 2

id	s_id	amount	date_from	date_to
1	33	100	2012-03-12 00:00:00	2022-01-01 00:00:00
2	33	200	2018-03-12 00:00:09	2021-02-28 00:00:00
3	33	300	2021-03-01 00:00:00	2021-03-31 00:00:00
4	33	400	2021-03-07 00:00:00	2021-03-12 00:00:00

How to select row with appropriate id where s_date between date_from and date_to most close to date_from\date_to range? In my case most appropriate rows must be:

id	s_id	s_date	amount
1	33	2021-03-05 10:36:59	300
2	33	2021-03-06 10:36:59	300
3	33	2021-03-09 10:36:59	400
4	33	2021-03-10 13:36:59	400
5	33	2021-03-11 12:36:59	400
6	33	2021-03-12 09:00:59	400
7	33	2021-03-13 13:36:59	300
8	33	2021-03-14 18:00:00	300
9	33	2021-03-15 18:00:00	300
10	33	2021-03-16 13:00:00	300
11	33	2021-03-17 18:00:00	300
12	33	2021-03-18 14:00:00	300
13	33	2021-04-01 18:00:00	100
14	33	2021-05-02 14:00:00	100

Thank you!

Solution

You can get the mid date for each pair of date_from and date_to with:

(UNIX_TIMESTAMP(date_from) + UNIX_TIMESTAMP(date_to)) / 2

Then find the absolute difference from s_date and sort by that:

SELECT *
FROM tablename
ORDER BY ABS(UNIX_TIMESTAMP(s_date) - ((UNIX_TIMESTAMP(date_from) + UNIX_TIMESTAMP(date_to)) / 2))

You can apply LIMIT 2 to get the 2 most appropriate ids.
See the demo.