需要来自MySql数据库的日期块数组 [英] Need an array of date blocks from MySql Database
问题描述
好的,我有一个包含StartDate和EndDate的行的数据库表.我需要做的是从中返回消耗的时间.
Ok, I have a database table of rows with a StartDate and an EndDate. What I need to do is return blocks of consumed time from that.
例如,如果我有3行,如下所示:
So, for example, if I have 3 rows as follows:
RowID StartDate EndDate
1 2011-01-01 2011-02-01
2 2011-01-30 2011-02-20
3 2011-03-01 2011-04-01
然后,所用时间的块如下:
then the blocks of used time would be as follows:
2011-01-01至2011-02-20和2011-03-01至2011-04-01
2011-01-01 to 2011-02-20 and 2011-03-01 to 2011-04-01
是否有一种简单的方法可以从MySql数据库中提取该数据?欢迎任何建议!
Is there an easy method of extracting that from a MySql database? Any suggestions welcome!
推荐答案
看下面的图,它表示一些重叠的时间段
Look at the diagram below which represents some overlapping time periods
X----| |--------| |------X
|-------X X------------X
|----| X----|
任何连续时间段的开始或结束(用X
标记)都不在任何其他时间段之内.如果我们确定这些时间,我们可以取得一些进展.
The beginning or end of any contiguous time period, marked with an X
doesn't fall inside any other time period. If we identify these times we can make some progress.
此查询标识边界.
SELECT boundary FROM
(
-- find all the lower bounds
SELECT d1.StartDate AS boundary, 'lower' as type
FROM dates d1
LEFT JOIN dates d2 ON (
d1.StartDate > d2.StartDate
AND
d1.StartDate < d2.EndDate
)
WHERE d2.RowId IS NULL
GROUP BY d1.StartDate
UNION
-- find all the upper bounds
SELECT d1.EndDate AS boundary, 'upper' as type
FROM dates d1
LEFT JOIN dates d2 ON (
d1.EndDate > d2.StartDate
AND
d1.EndDate < d2.EndDate
)
WHERE d2.RowId IS NULL
GROUP BY d1.StartDate
) as boundaries
ORDER BY boundary ASC
此查询对您的数据的结果是
The result of this query on your data is
boundry | type
------------------
2011-01-01 | lower
2011-02-20 | upper
2011-03-01 | lower
2011-04-01 | upper
您想要的日期范围在上面显示的连续上下限之间.只需进行一些后期处理,就可以轻松找到这些内容.
The date ranges you are after are between consecutive lower and upper bounds shown abouve. With a bit of post processing, these can easily be found.
这篇关于需要来自MySql数据库的日期块数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!