Separate hourly data into different days based on the hour and minute time.

I have a time table that looks as follows: (1x135 timetable)

Each column represents an hour and minute timestamp, see below for example:

05:42:00 AM 11:02:00 AM 03:28:00 PM  08:53:00 PM   12:44:00 AM     02:07:00 AM     06:43:00 AM ...

and this goes on for 135 columns, the times represent a recording of a variable on particular dates which I have in a seperate timetable. What I would like to do is seperate the hours into days, and attach the date.

So I would hopefully end up with something like this:

11/1/2021 05:42:00 AM
11/1/2021 11:02:00 AM
11/1/2021 03:28:00 PM
11/1/2021 08:53:00 PM
12/1/2021 12:44:00 AM
12/1/2021 02:07:00 AM
12/1/2021 06:43:00 AM
12/1/2021 08:31:00 AM
12/1/2021 09:25:00 AM
12/1/2021 01:55:00 PM
12/1/2021 09:53:00 PM
13/1/2021 06:42:00 AM
13/1/2021 11:49:00 AM

The first day would include the first four values, that is time_table(1,1:4) because they occurred between the first 24 hour interval. The second day would include the next seven values after that, that is time_table(1,5:11), because they occurred in the second 24 hour interval and so on.

Is there any way to attach a date to the values between the first 24 hour interval, for example the first four values would have the date 11/1,2021, the second 24 hours worth of values would have 12/1/2021, and so fourth.

Any help much appreciated, thank you

[dpb]

Drat...apologies. I formatted the post to use the code formatting so folks could use Copy and inadvertently wiped the original line -- sorry, wasn't intentional at all. Could you re-post that data in its entirety? Again, my bad...

--dpb

[dpb]

Is the original file constructed in such a manner? Despite my foo-pah above, it would be easier for folks to write specific code to help if would attach the original file so can look at importing it again...or your data as you do have it as a .mat file a close second. Although I always like to see if I can fix the problem before it becomes a problem which would be going back to the file import step to see if can get what want from the git-go instead of having to do a fix-up later...

Answer 1

[Peter Perkins]

What's called for here is a nice mix of old and new. The new is datetimes and durations, the old is finding the day boundaries using diff, <, and cumsum:

>> times = ["05:42:00 AM" "11:02:00 AM" "03:28:00 PM" "08:53:00 PM" "12:44:00 AM" ...
    "02:07:00 AM" "06:43:00 AM" "08:31:00 AM" "09:25:00 AM" "01:55:00 PM" ...
    "09:53:00 PM" "06:42:00 AM" "11:49:00 AM"]';
>> times = datetime(times,"Format","hh:mm:ss aa");
>> timesOfDay = timeofday(times)
timesOfDay = 
13×1 duration array
05:42:00
11:02:00
15:28:00
20:53:00
00:44:00
02:07:00
06:43:00
08:31:00
09:25:00
13:55:00
21:53:00
06:42:00
11:49:00
>> dayNum = cumsum([false; diff(timesOfDay)<0])
dayNum =
0
0
0
0
1
1
1
1
1
1
1
2
2
>> days = datetime(2021,11,1) + caldays(dayNum)
days = 
13×1 datetime array
01-Nov-2021
01-Nov-2021
01-Nov-2021
01-Nov-2021
02-Nov-2021
02-Nov-2021
02-Nov-2021
02-Nov-2021
02-Nov-2021
02-Nov-2021
02-Nov-2021
03-Nov-2021
03-Nov-2021
>> timestamps = days + timesOfDay
timestamps = 
13×1 datetime array
01-Nov-2021 05:42:00
01-Nov-2021 11:02:00
01-Nov-2021 15:28:00
01-Nov-2021 20:53:00
02-Nov-2021 00:44:00
02-Nov-2021 02:07:00
02-Nov-2021 06:43:00
02-Nov-2021 08:31:00
02-Nov-2021 09:25:00
02-Nov-2021 13:55:00
02-Nov-2021 21:53:00
03-Nov-2021 06:42:00
03-Nov-2021 11:49:00