Skip to content
Advertisement

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_fromdate_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!

Advertisement

Answer

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.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement