组合/合并日期范围的算法 [英] 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)?

非常感谢任何建议.

谢谢！

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

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屋！