[SQL] - Separate timestamps on several lines

Transact-SQL
1

Hi !
I have a dataset with timestamps. For example

| ID | start_timestamp | end_timestamp | start_end_duration_hours
| 56 | 2022-11-01 22:30:00 | 2022-11-02 01:30:00 | 3

I want to retrieve several lines, one line per hour

| ID | start_timestamp | end_timestamp | start_end_duration_hours
| 56 | 2022-11-01 22:30:00 | 2022-11-01 23:00:00 | 0.5
| 56 | 2022-11-01 23:00:00 | 2022-11-02 00:00:00 | 1
| 56 | 2022-11-02 00:00:00 | 2022-11-02 01:00:00 | 1
| 56 | 2022-11-02 01:00:00 | 2022-11-02 01:30:00 | 0.5

I have difficulties resolving this query, do you have any piece of advice for it ?
Thanks
Fopaln

2

To do this - you need to generate the hourly rows between the minimum start and maximum end time. Then you can join to that table from your dataset where the start_timestamp is less than the start hour or the end_timestamp is greater than the end hour.

Once you have that set - you can then determine the duration by checking if the start_timestamp is greater than that rows start hour - if true, use the start_timestamp to determine duration. If not true, use start hour for that row. The same with end_timestamp - if end_timestamp is less than end_hour use end_timestamp else use end hour.

3

Ok thanks for your answer.

The second part is clear and helpful, thanks.

For the first part, I have no idea on how to generate several hourly rows is there some function that can be used for that ?

Fopaln

5

hi

hope this helps

the last step i have to figure out is the end date time

create data script

drop table #temp

create table #temp ( ID int , start_timestamp datetime , end_timestamp datetime , start_end_duration_hours int )

insert into #temp select 56 ,'2022-11-01 22:30:00','2022-11-02 01:30:00', 3

select * from #temp

; with rec_cte as
(
   select 
       N=0 
     , dateadd(mi,0*60,start_timestamp) as ok  
   from  
       #temp
   union all 
     select 
	       N+1
		 , case when datepart(mi,ok) = 30 then dateadd(mi,(N+1)*30,rec_cte.ok) 
		        when datepart(mi,ok) = 00 then dateadd(mi,60,rec_cte.ok) 				
		   end   
	 from 
	     rec_cte , #temp
     where 
	     rec_cte.ok < #temp.end_timestamp 
)
select 
  *
from 
  rec_cte

image