Split time period into shorter intervals

[tommasoscarpa1]

Hello,

I have this data set:

event, start_time, end_time
EV1, 2024/07/11 12:05, 2024/07/11 13:05
EV2, 2024/07/11 21:13, 2024/07/12 04:13
EV3, 2024/07/13 11:22, 2024/07/14 02:44

I need to split the time intervals into hourly intervals. One interval for each row.

Eventually I'm looking for an output like this:

event, start_time, end_time
EV1, 2024/07/11 12:05, 2024/07/11 13:00
EV1, 2024/07/11 13:00, 2024/07/11 13:05
EV2, 2024/07/11 21:13, 2024/07/12 22:00
EV2, 2024/07/11 22:00, 2024/07/12 23:00
EV2, 2024/07/11 23:00, 2024/07/12 00:00
EV2, 2024/07/11 00:00, 2024/07/12 01:00
EV2, 2024/07/11 01:00, 2024/07/12 02:00
EV2, 2024/07/11 02:00, 2024/07/12 03:00
EV2, 2024/07/11 03:00, 2024/07/12 04:00
EV2, 2024/07/11 04:00, 2024/07/12 04:13
EV3, 2024/07/13 11:22, 2024/07/14 12:00
EV3, 2024/07/13 12:00, 2024/07/14 13:00
EV3, 2024/07/13 13:00, 2024/07/14 14:00
EV3, 2024/07/13 14:00, 2024/07/14 15:00
EV3, 2024/07/13 15:00, 2024/07/14 16:00
EV3, 2024/07/13 16:00, 2024/07/14 17:00
EV3, 2024/07/13 17:00, 2024/07/14 18:00
EV3, 2024/07/13 18:00, 2024/07/14 19:00
EV3, 2024/07/13 19:00, 2024/07/14 20:00
EV3, 2024/07/13 20:00, 2024/07/14 21:00
EV3, 2024/07/13 21:00, 2024/07/14 22:00
EV3, 2024/07/13 22:00, 2024/07/14 23:00
EV3, 2024/07/13 23:00, 2024/07/14 00:00
EV3, 2024/07/13 00:00, 2024/07/14 01:00
EV3, 2024/07/13 01:00, 2024/07/14 02:00
EV3, 2024/07/13 02:00, 2024/07/14 02:44

I tried using bin or timechart command but they don't work.

Do you guys have any sugestion?

Thank you,

Tommaso

[ITWhisperer]

| eval time_start=strptime(time_start,"%Y/%m/%d %H:%M")
| eval time_end=strptime(time_end,"%Y/%m/%d %H:%M")
| eval time_start=mvrange(time_start,time_end,60*60)
| mvexpand time_start
| eval time_end=time_start+(60*60)
| eval time_start=strftime(time_start,"%Y/%m/%d %H:%M")
| eval time_end=strftime(time_end,"%Y/%m/%d %H:%M")

[tommasoscarpa1]

It works like a charm!!

Thank you!!

EDIT-----
No. Sorry My bad. I realized I forgot to mention that timestamps are not always at :00. I've updated the question.
Would you have any suggestion on how to round use your solution to keep into account the minutes?

Thank you

[tommasoscarpa1 · Accepted solution]

I got it:

| eval start_time=strptime(start_time,"%Y/%m/%d %H:%M")
| eval end_time=strptime(end_time,"%Y/%m/%d %H:%M")
| eval range_start_time = relative_time(start_time, "@h")
| eval range_end_time = relative_time(end_time, "+1h@h")
| eval range_start_time=mvrange(range_start_time, range_end_time, "1h")
| mvexpand range_start_time
| eval range_end_time=range_start_time+3600
| eval end_time=min(range_end_time, end_time)
| eval start_time=max(range_start_time, start_time)
| eval start_time=strftime(start_time,"%Y/%m/%d %H:%M")
| eval end_time=strftime(end_time,"%Y/%m/%d %H:%M")
| eval range_start_time=strftime(range_start_time,"%Y/%m/%d %H:%M")
| eval range_end_time=strftime(range_end_time,"%Y/%m/%d %H:%M")