在sql server中以5分钟的间隔分割时间 [英] Split time with 5 mins interval in sql server

问题描述

嗨...

我有以下数据

Hi...
I have the data like below

Id     Time       Location
1     08:00     Test Location
2     08:15     Test Location
3     08:30     Test Location
4     08:45     Test Location
5     09:00     Test Location 

我希望得到如下所示的结果

and I would like to get the result as shown below

Id     Time       Location
1     08:00     Test Location
1     08:05     Test Location
1     08:10     Test Location
2     08:15     Test Location
2     08:20     Test Location
2     08:25     Test Location
3     08:30     Test Location
3     08:35     Test Location
---------------------------- 

请帮忙搞定他的结果



谢谢

Pramod



我有什么尝试过：



我尝试使用日期差异，但无法得到结果...

Please help to get this result

Thanks
Pramod

What I have tried:

I tried with date diff but am not able to get the result...

推荐答案

我真的不喜欢在SQL Server中使用程序循环，这就是我提供这种替代解决方案的原因。


你可以通过使用获得你想要的结果一个递归的CTE。



我用

I really dislike using procedural loops in SQL Server which is why I'm offering this alternative solution.

You can get the results you want by using a recursive CTE.

I created some test data with

CREATE TABLE TestLocation 
(Id INT IDENTITY(1,1),[Time] NVARCHAR(50),Location NVARCHAR(50))
 
INSERT INTO TestLocation VALUES
('08:00','Test Location'),
('08:15','Test Location'),
('08:30','Test Location1'),
('08:45','Test Location1'),
('09:00','Test Location2')

它类似于你在你的数据中提供的数据问题，但我想在测试我的查询时更改位置值。



此单个查询然后为您提供您想要的结果

It's similar to the data you presented in your question but I wanted to change the location values when testing my query.

This single query then gives you the results you wanted

;with CTE AS
(
	SELECT  cast([Time] AS Time) AS [Time], 1 as RLevel, Id, Location
	FROM TestLocation

	UNION ALL
	SELECT CAST(dateadd(Minute, 5, [Time]) AS Time), RLevel + 1, Id, Location
	FROM CTE
	WHERE RLevel < 3 -- i.e. 15 minutes divided by the 5 added
)
select Id, [Time], Location
from CTE
WHERE [Time] <=  (SELECT MAX([Time]) FROM TestLocation)
order by [Time]

对于原始数据中的每条记录，此rCTE正在创建更多行，第一行添加5分钟时间，因为这是递归的，下一行在新行上增加5分钟。



注意这只适用于原始数据以15分钟为间隔而没有任何中断的情况。 />


以5分钟为间隔列出08:00至09:00（含）之间所有时间的最简单方法是

For every record in your original data this rCTE is creating more rows, the first adds 5 minutes onto the time and because this is recursive, the next row adds 5 minutes onto the new row.

Note this only works if the original data is in 15 minute intervals without any breaks.

The easiest way to list all of the times between 08:00 to 09:00 (inclusive) in 5 minute intervals is

;with CTE AS
(
	SELECT  CAST('08:00' AS Time) AS datum
	UNION ALL
	SELECT dateadd(Minute, 5, datum)
	FROM CTE
	WHERE dateadd(Minute, 5, datum) < dateadd(Minute, 65, CAST('08:00' AS Time))
)
select * from CTE

我在下面考虑过此示例的表

I have considered below table for this example

CREATE TABLE tblTestLocation (Id INT,[Time] NVARCHAR(50),Location NVARCHAR(50))

INSERT INTO tblTestLocation 
SELECT 1,'08:00','Test Location' UNION
SELECT 2,'08:15','Test Location' UNION
SELECT 3,'08:30','Test Location' UNION
SELECT 4,'08:45','Test Location' UNION
SELECT 5,'09:00','Test Location'

逻辑下面会给你所需的结果

Below logic will give you required result

DECLARE @tmpTestLocation AS TABLE(Id INT,[Time] NVARCHAR (50) ,Location NVARCHAR(50))
DECLARE @FinaltmpTestLocation AS TABLE(Id INT,[Time] NVARCHAR (50) ,Location NVARCHAR(50))

DECLARE @ttlRows AS INT
DECLARE @rowindex as INT
DECLARE @CurrentTime AS DateTime
DECLARE @Id AS Int
DECLARE @Location AS NVARCHAR(50)
DECLARE @NextTime AS DateTime

SELECT @ttlRows =count(Id) FROM tblTestLocation
SET @rowindex =1

WHILE @rowindex  <= @ttlRows
BEGIN
  
  SELECT @Id=Id,@Location=Location,@CurrentTime=CAST([Time] AS DATETIME) FROM tblTestLocation WHERE Id=@rowindex 
  SELECT @NextTime=CAST([Time] AS DATETIME)  FROM tblTestLocation WHERE Id=(@rowindex +1)
    INSERT INTO @tmpTestLocation   
    SELECT Id,[Time],Location FROM tblTestLocation WHERE Id=@rowindex 

	WHILE @CurrentTime < (@NextTime -'00:05:00')
	BEGIN
       SET @CurrentTime =@CurrentTime +'00:05:00'
      
       INSERT INTO @tmpTestLocation   
       SELECT @Id,convert(char(5), @CurrentTime, 108),@Location
              
    End
SET @rowindex  =@rowindex  +1  
END
select  * from @tmpTestLocation

这篇关于在sql server中以5分钟的间隔分割时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！