按小时划分时间间隔

Question

我有一个数据集：

  tbldataid          TS                     EndTS
> HX32.3401 10/2/2017 11:49:34 PM   10/3/2017 12:01:57 AM
> HX32.3403 10/3/2017 12:02:48 AM   10/3/2017 12:08:34 AM
> HX32.3425 10/3/2017 2:50:57 AM    10/3/2017 2:50:58 AM
> HX32.3428 10/3/2017 4:06:15 AM    10/3/2017 6:09:19 AM

我想基本上在一个小时内“拆分”这些时间间隔，例如：

tbldataid               TS                EndTS
HX32.3401   10/2/2017 11:49:34 PM   10/2/2017 11:59:99 PM
HX32.3401   10/2/2017 12:00:00 PM   10/3/2017 12:01:57 AM
HX32.3403   10/3/2017 12:02:48 AM   10/3/2017 12:08:34 AM
HX32.3425   10/3/2017 2:50:57 AM    10/3/2017 2:50:58 AM
HX32.3428   10/3/2017 4:06:15 AM    10/3/2017 4:59:99 AM
HX32.3428   10/3/2017 5:00:00 AM    10/3/2017 5:59:99 AM
HX32.3428   10/3/2017 6:00:00 AM    10/3/2017 6:09:19 AM

到目前为止，这是我的代码（该想法的积分发给@DumitrescuBogdan，将通话数据分为15分钟间隔）：

SELECT [tbldataid],[TS],
   IIF([EndTS]<=dateadd("n",60*((datediff("n",0,[TS]/60))+.99),0), 
        [EndTS], 
        dateadd("n", 60*(datediff("n",0,[TS]/60)+.99),0)) 
        as [End] 
FROM tbldata


UNION ALL

SELECT t2.[tbldataid], t2.[TS],
   IIF(t1.[EndTS]<=dateadd("n",60*((datediff("n",0, t2.[EndTS])/60)+1),0), 
         t1.[EndTS], 
         dateadd("n",60*((datediff("n",0,t2.[EndTS])/60)+1),0)) 
         as [End] 
FROM 
   tbldata t1 
   LEFT JOIN tbldata t2
   ON t1.[tbldataid]=t2.[tbldataid]
Where t2.[EndTS]<IIF(t1.[EndTS]<=dateadd("n",60*((datediff("n",0,t2.[EndTS])/60)+1),0),
                                   t1.[EndTS],
                                   dateadd("n",60*((datediff("n",0,t2.[EndTS])/60)+1),0));

后半部分（在“全部合并”之后）不产生任何结果，前半部分产生以下结果：

tbldataid             TS                     End
HX32.3401   10/2/2017 11:49:34 PM   10/2/2017 11:59:00 PM
HX32.3403   10/3/2017 12:02:48 AM   10/3/2017 12:08:34 AM
HX32.3425   10/3/2017 2:50:57 AM    10/3/2017 2:50:58 AM
HX32.3428   10/3/2017 4:06:15 AM    10/3/2017 4:59:00 AM

谢谢，我是初学者。我了解第一部分；坦白地说，我不明白下半场

我愿意接受其他解决方案。

我正在使用MS-Access 2010

Answer 1

这可以通过 笛卡尔 查询来完成：

SELECT DISTINCT 
    tblData.tblDataId, 
    IIf([TimeStart] > DateAdd("h",[Factor],CDate(Fix([Timestart]*24)/24)),
        [TimeStart],
        DateAdd("h",[Factor],CDate(Fix([Timestart]*24)/24))) AS TSStart, 
    IIf([TimeEnd] < DateAdd("s",3599,DateAdd("h",[Factor],CDate(Fix([Timestart]*24)/24))),
        [TimeEnd],
        DateAdd("s",3599,DateAdd("h",[Factor],CDate(Fix([Timestart]*24)/24)))) AS TSEnd
FROM 
    qdxFactor, 
    tblData
WHERE
    qdxFactor.Factor Between 0 And DateDiff("h",[TimeStart],[TimeEnd]);

使用其他已保存的笛卡尔查询（ qdxFactor ）：

SELECT DISTINCT 
    [Tens]+[Ones] AS Factor, 
    10*Abs([Deca].[id] Mod 10) AS Tens, 
    Abs([Uno].[id] Mod 10) AS Ones
FROM 
    MSysObjects AS Uno, 
    MSysObjects AS Deca;

结果：

tblDataId   TSStart             TSEnd
    3401    2017-10-02 23:49:34 2017-10-02 23:59:59
    3401    2017-10-03 00:00:00 2017-10-03 00:01:57
    3403    2017-10-03 00:02:48 2017-10-03 00:08:34
    3425    2017-10-03 02:50:57 2017-10-03 02:50:58
    3428    2017-10-03 04:06:15 2017-10-03 04:59:59
    3428    2017-10-03 05:00:00 2017-10-03 05:59:59
    3428    2017-10-03 06:00:00 2017-10-03 06:09:19

第二个示例：

tblDataId   TimeStart           TimeEnd
    3430    2017-10-07 02:08:24 2017-10-07 14:09:30

结果：

tblDataId   TSStart             TSEnd
    3430    2017-10-07 02:08:24 2017-10-07 02:59:59
    3430    2017-10-07 03:00:00 2017-10-07 03:59:59
    3430    2017-10-07 04:00:00 2017-10-07 04:59:59
    3430    2017-10-07 05:00:00 2017-10-07 05:59:59
    3430    2017-10-07 06:00:00 2017-10-07 06:59:59
    3430    2017-10-07 07:00:00 2017-10-07 07:59:59
    3430    2017-10-07 08:00:00 2017-10-07 08:59:59
    3430    2017-10-07 09:00:00 2017-10-07 09:59:59
    3430    2017-10-07 10:00:00 2017-10-07 10:59:59
    3430    2017-10-07 11:00:00 2017-10-07 11:59:59
    3430    2017-10-07 12:00:00 2017-10-07 12:59:59
    3430    2017-10-07 13:00:00 2017-10-07 13:59:59
    3430    2017-10-07 14:00:00 2017-10-07 14:09:30