按小时划分时间间隔 [英] Split time intervals on the hour
问题描述
我有一个数据集:
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
我想基本上在一个小时内拆分"这些时间间隔，例如:
I would like to to essentially 'split' these intervals on the hour, such as:
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
到目前为止，这是我的代码(该想法的积分发给@Dumitrescu Bogdan，
Here is my code so far (credit for the idea goes to @Dumitrescu Bogdan, Split call data to 15 minute intervals):
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));
下半部分(在全部合并"之后)不会产生任何结果，上半部分会产生以下结果:
The second half (after 'union all') doesn't produce any result, the first half produces the following:
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
谢谢，我是初学者.我了解第一部分；坦白地说，我听不懂下半场
Thanks, I'm a beginner. I understand the first part; admittedly I don't understand the second half
我愿意接受其他解决方案.
I'm open to other solutions.
我正在使用MS-Access 2010
I'm using MS-Access 2010
推荐答案
这可以通过 Cartesian 查询来完成:
This can be done with a Cartesian query:
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 ):
using this other saved Cartesian query (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
这篇关于按小时划分时间间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
