合并/合并日期范围的算法 [英] Algorithm to combine / merge date ranges

问题描述

我正在尝试找到有关如何将日期范围合并到一个数据库记录(数组元素)中的最佳方法.

I am trying to find the best way on how to merge date ranges into one database record (array element).

这是我拥有的数据:

  Array
(
    [0] => Array
        (
            [id] => 18298
            [start_date] => 2011-07-09
            [end_date] => 2011-10-01
        )

    [1] => Array
        (
            [id] => 18297
            [start_date] => 2011-06-01
            [end_date] => 2011-06-30
        )

    [2] => Array
        (
            [id] => 17113
            [start_date] => 2011-03-31
            [end_date] => 2011-05-31
        )

    [3] => Array
        (
            [id] => 20555
            [start_date] => 2011-01-03
            [end_date] => 2011-03-31
        )
)

将它们组合后，数组(或数据库)应如下所示:

And after we combine them, array (or database) should look like this:

Array
(
    [0] => Array
        (
            [merged_ids] => 18298
            [start_date] => 2011-07-09
            [end_date] => 2011-10-01
        )

    [1] => Array
        (
            [merged_ids] => 18297, 17113, 20555
            [start_date] => 2011-01-03
            [end_date] => 2011-06-30
        )
)

是否有任何算法可以遍历所有元素/范围并将它们组合在一起?通过数据库(MYSQL)或编码(PHP)哪种方法更好/更容易?

Is there any algorithm to go through all elements/ranges and combine them? Which way is better/easier to do - through database (MYSQL) or coding (PHP)?

任何建议都将受到高度赞赏.

Any advise is highly appreciated.

谢谢！

更新:抱歉，我没有提供足够的信息:我们应该合并任何连续和重叠的日期范围.

UPDATE: Sorry, I didn't provide enough info: we should merge any continuous and overlapping date ranges.

推荐答案

按开始日期排序.

然后遍历并检查下一项的开始日期是在当前结束日期之前还是之后.如果是这样，则将下一个合并到当前的一个中.然后继续.

Then iterate through and check for if the next item's start date is before or directly after the current one's end date. If it is, then merge the next one into the current one. Then continue.

这篇关于合并/合并日期范围的算法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！