add 'dd-MMM-yyyy' data to datetime array in form 'HH:mm:ss:SSS' with rollover

I need to add calendar date information to an array of datetime data in the form 'HH:mm:ss:SSS', i have the date that the data starts being collected on and a snippet of the data looks like this

'23:59:59:873'
'23:59:59:893'
'23:59:59:933'
'23:59:59:963'
'00:00:00:003'
'00:00:00:043'
'00:00:00:073'
'00:00:00:103'

I know how to reformat the datetime data so it includes the calendar information and assign a year month and day to the data with

TimeData.Format = 'dd-MMM-yyyy HH:mm:ss:SSS';
TimeData.Year = startTime.Year;
TimeData.Month = startTime.Month;
TimeData.Day = startTime.Day;

This makes the data look like

'04-Jan-2023 23:59:59:873'
'04-Jan-2023 23:59:59:893'
'04-Jan-2023 23:59:59:933'
'04-Jan-2023 23:59:59:963'
'04-Jan-2023 00:00:00:003'
'04-Jan-2023 00:00:00:043'
'04-Jan-2023 00:00:00:073'
'04-Jan-2023 00:00:00:103'

Im wondering how to go about providing the TimeData array a start date, and have it update the date as the data rolls over 24 hours to a new day, having the data look like this

'04-Jan-2023 23:59:59:873'
'04-Jan-2023 23:59:59:893'
'04-Jan-2023 23:59:59:933'
'04-Jan-2023 23:59:59:963'
'05-Jan-2023 00:00:00:003'
'05-Jan-2023 00:00:00:043'
'05-Jan-2023 00:00:00:073'
'05-Jan-2023 00:00:00:103'

Answer 1

[Star Strider]

I’m not certain how robust this is, however it seems to work in this example —

Times = ['23:59:59:873'
        '23:59:59:893'
        '23:59:59:933'
        '23:59:59:963'
        '00:00:00:003'
        '00:00:00:043'
        '00:00:00:073'
        '00:00:00:103'];

Time = datetime([Times; Times; Times], 'InputFormat','HH:mm:ss:SSS');   % Repeat To Test Code

DI = cumsum([0; diff(hour(Time))<0]);                                  % Day Increment

Date = datetime('04-Jan-2023') + days(DI);                              % 'Date' Vector
DateTime = Date + timeofday(Time);                                      % Date + Time
DateTime.Format = 'dd-MMM-yyyy HH:mm:ss.SSS'

DateTime = 24×1 datetime array
   04-Jan-2023 23:59:59.873
   04-Jan-2023 23:59:59.893
   04-Jan-2023 23:59:59.933
   04-Jan-2023 23:59:59.963
   05-Jan-2023 00:00:00.003
   05-Jan-2023 00:00:00.043
   05-Jan-2023 00:00:00.073
   05-Jan-2023 00:00:00.103
   05-Jan-2023 23:59:59.873
   05-Jan-2023 23:59:59.893
   05-Jan-2023 23:59:59.933
   05-Jan-2023 23:59:59.963
   06-Jan-2023 00:00:00.003
   06-Jan-2023 00:00:00.043
   06-Jan-2023 00:00:00.073
   06-Jan-2023 00:00:00.103
   06-Jan-2023 23:59:59.873
   06-Jan-2023 23:59:59.893
   06-Jan-2023 23:59:59.933
   06-Jan-2023 23:59:59.963
   07-Jan-2023 00:00:00.003
   07-Jan-2023 00:00:00.043
   07-Jan-2023 00:00:00.073
   07-Jan-2023 00:00:00.103

It works by creating the ‘DI’ (‘Day Increment’) vector, and adding it appropriately to the successive days. Try it on your complete data set to see if it gives the desired result.

EDIT — (12 Jan 2023 at 00:36)

Changed ‘~=0’ to ‘<0’ in the ‘DI’ calculation.

.

[Leo Rogers]

It looks like thisll work, definitely puts me on the right track. I gotta run so I'll give it a try tomorrow then close the question. Thanks for the help!

[dpb]

One thing have to be careful of in such manipulations is whether the timestamps are in a timezone with DST and if the time in question covers the witching days in spring/fall when it changes.

If this could be a case in practice, see the timeofday doc for an example of how to deal with...

[dpb]

Times = ['23:59:59:873'
    '23:59:59:893'
    '23:59:59:933'
    '23:59:59:963'
    '00:00:00:003'
    '00:00:00:043'
    '00:00:00:073'
    '00:00:00:103'];
Time = datetime(Times,'InputFormat','HH:mm:ss:SSS');
Time=[Time;Time(5:end)+hours(1);];      % add an hour turnover besides day
Time=repmat(Time,2,1);          % and another copy to test
DI = cumsum([0; diff(hour(Time))~=0]);                                  % Day Increment
Date = datetime('04-Jan-2023') + days(DI);                              % 'Date' Vector
DateTime = Date + timeofday(Time);                                      % Date + Time
DateTime.Format = 'dd-MMM-yyyy HH:mm:ss.SSS'

DateTime = 24×1 datetime array
   04-Jan-2023 23:59:59.873
   04-Jan-2023 23:59:59.893
   04-Jan-2023 23:59:59.933
   04-Jan-2023 23:59:59.963
   05-Jan-2023 00:00:00.003
   05-Jan-2023 00:00:00.043
   05-Jan-2023 00:00:00.073
   05-Jan-2023 00:00:00.103
   06-Jan-2023 01:00:00.003
   06-Jan-2023 01:00:00.043
   06-Jan-2023 01:00:00.073
   06-Jan-2023 01:00:00.103
   07-Jan-2023 23:59:59.873
   07-Jan-2023 23:59:59.893
   07-Jan-2023 23:59:59.933
   07-Jan-2023 23:59:59.963
   08-Jan-2023 00:00:00.003
   08-Jan-2023 00:00:00.043
   08-Jan-2023 00:00:00.073
   08-Jan-2023 00:00:00.103
   09-Jan-2023 01:00:00.003
   09-Jan-2023 01:00:00.043
   09-Jan-2023 01:00:00.073
   09-Jan-2023 01:00:00.103

What afraid of, @Star Strider, the test for ~0 turns over a day for every hour change, not just rollover at midnight...

[Star Strider]

@Leo Rogers — My pleasure!

The ‘DI’ vector is set to increment only when there is an abrupt negative change in the hours difference. It shouldn’t increment in any othe condition, providing the hours are always in a 24-hour cycle. A 12-hour cycle with AM and PM would break it, and I would need to figure out a way to deal with that.

Note added in proof —

v = linspace(0, 23, 4).'*ones(1,5);
v = v(:).';
DI = cumsum([0 diff(v)<0]);

A = [fix(v); DI]

A = 2×20

0 7 15 23 0 7 15 23 0 7 15 23 0 7 15 23 0 7 15 23 0 0 0 0 1 1 1 1 2 2 2 2 3 3 3 3 4 4 4 4

EDIT — (12 Jan 2023 00:40)

Added example.

.

Answer 2

[dpb]

Let's try a little mod on @Star Strider's idea...

Times = ['23:59:59:873'
    '23:59:59:893'
    '23:59:59:933'
    '23:59:59:963'
    '00:00:00:003'
    '00:00:00:043'
    '00:00:00:073'
    '00:00:00:103'];
Time = datetime(Times,'InputFormat','HH:mm:ss:SSS');
Time=[Time;Time(5:end)+hours(1);];                      % add an hour turnover besides day
Time=repmat(Time,2,1);                                  % and another copy to test
DI = cumsum([0; diff(hour(Time))<0]);                                  % Day Increment
Date = datetime('04-Jan-2023') + days(DI);                              % 'Date' Vector
DateTime = Date + timeofday(Time);                                      % Date + Time
DateTime.Format = 'dd-MMM-yyyy HH:mm:ss.SSS'

DateTime = 24×1 datetime array
   04-Jan-2023 23:59:59.873
   04-Jan-2023 23:59:59.893
   04-Jan-2023 23:59:59.933
   04-Jan-2023 23:59:59.963
   05-Jan-2023 00:00:00.003
   05-Jan-2023 00:00:00.043
   05-Jan-2023 00:00:00.073
   05-Jan-2023 00:00:00.103
   05-Jan-2023 01:00:00.003
   05-Jan-2023 01:00:00.043
   05-Jan-2023 01:00:00.073
   05-Jan-2023 01:00:00.103
   05-Jan-2023 23:59:59.873
   05-Jan-2023 23:59:59.893
   05-Jan-2023 23:59:59.933
   05-Jan-2023 23:59:59.963
   06-Jan-2023 00:00:00.003
   06-Jan-2023 00:00:00.043
   06-Jan-2023 00:00:00.073
   06-Jan-2023 00:00:00.103
   06-Jan-2023 01:00:00.003
   06-Jan-2023 01:00:00.043
   06-Jan-2023 01:00:00.073
   06-Jan-2023 01:00:00.103

OK, at least a first blush it appears that simply checking to be sure the hour rollover is <0 will work; again the caveat about dealing with DST.

I've always wondered why TMW didn't introduce some tools with the timetable or just with datetime class to help with the rollover issues -- I've found several times that the rollover bug bit and no real help available to deal with it at higher level. I can't think of the specific examples at the moment, unfortunately, but I do recall having had difficulties...

Answer 3

[Leo Rogers]

Thank you both, totally works, i was worried it might take a while to crunch theres alot of data. But works perfect. Ya i think its odd matlab doesnt have anything prebuilt for something like this. It'd be handy to just have a built in function to provide a start date and datetime or duration array and have it just add the dates to it. I feel like thats a situation that comes up enough its warented. Anyway, your solutions as a function

% provide Times as array of datetime data, and startDate as a datetime
% data object at the desired start date, returns datetime data with date
% information
function dateTimes = dateTimeArray(Times, startDate)
    DI = cumsum([0; diff(hour(Times))<0]);
    Y = startDate.Year;
    M = startDate.Month;
    D = startDate.Day;
    dates = datetime(Y,M,D) + days(DI);
    dateTimes = dates + timeofday(Times);
    dateTimes.Format = 'dd-MMM-yyyy HH:mm:ss.SSS';
end

Thanks again!

[Star Strider]

As always, my pleasure!

[youma]

well totally agreed with you, I'm kinda having the "same" issue. In my cas I have a Timestamp from a table with 5 others columns with values from 01 Jan 2022 00:00:00 to 31dec 2022 21:50:00. And I was doing some interpolation over the missing values, except that I keep having the major issue when I tried interpolated on a long period 20 missing days. Well those values are really missing in the table, I mean I could figured out they were missing cause I could see it in the plot (obviously). And now I was wondering how could create missing_data on my table, filled them up with Nan Values before doing my real work, the interpolation?

This is the trying code before I figured out the major issue: meteo_data_small_interp=meteo_data;

start_date = datetime(2022, 6, 19, 21, 0, 0);

end_date = datetime(2022, 7, 9, 0, 0, 0);

interval_length = 10;

intervals_per_hour = 60 / interval_length;

missing_data = NaN(20*24*intervals_per_hour, 4);

for h = 0:23

for m = 0:(intervals_per_hour-1)

interval_start = start_date + hours(h) + minutes(m*interval_length);

interval_end = interval_start + minutes(interval_length);

interval_data = meteo_data((meteo_data.TimeStamp >= interval_start) & (meteo_data.TimeStamp < interval_end), :);

if size(interval_data, 1) < 1

continue;

end

if ~isempty(interval_data) && any(isnan(interval_data(:, 2:5)))

before_dates = interval_start - days(20):-1:interval_start - days(39);

after_dates = interval_end + days(1):interval_end + days(20);

dates = [before_dates, after_dates];

date_data = meteo_data(ismember(meteo_data.TimeStamp, dates), :);

temp_mean = mean(date_data.AR_Temp_C);

hum_mean = mean(date_data.AR_Hum_ );

rad5_mean = mean(date_data.GlobalRadiation5DegressKWh);

rad45_mean = mean(date_data.GlobalRadiation45DegressKWh);

interval_data(isnan(interval_data(:, 2)), 2) = temp_mean;

interval_data(isnan(interval_data(:, 3)), 3) = hum_mean;

interval_data(isnan(interval_data(:, 4)), 4) = rad5_mean;

interval_data(isnan(interval_data(:, 5)), 5) = rad45_mean;

end

interval_idx = (h*intervals_per_hour) + m + 1;

missing_data(interval_idx, :) = interval_data(1, 2:5);

end

end

[dpb]

Is there a question buried in there somewhere, @youma?

If the timestamps are uniform but some are missing, then <retime> should do the trick to fill in the missing values using that sampling interval. It will also give you a choice of interpolation between zoh (zero-order hold or constant from preceding sample point) or foh (first-order hold which is linear interpolation).

That really isn't the same issue as the one of the subject Q? which was to add the missing date to a series when had only time-of-day; there was no date information available.

Your case has the full datetime info for the data that you do have; filling in the missing isn't difficult in that case.

If your data aren't precisely on the same sample rate and it is important to keep those precise times, then you can create a second timetable that covers the range of the first with uniform rates and then synchronize the two; it will keep all times that exist in either table and fill the data fields with missing values for the times not in the other. It all depends on precisely what it is that are trying to get.

[youma]

Hello,

So I did try the retime to fill as you said to fill out the missing data with NaN values using these lines :

new_times = meteo_data.TimeStamp;

dt=minutes(10)

meteo_full_data = retime(meteo_data, new_times,'fillwithmissing');

But all I get is this error message : Incorrect number or types of inputs or outputs for function 'retime'.

And I did use retime as it was showed in the help, still can't get it right. I've seen the similar issue in the use of retime, but no solve case yet. Care to help? thanks

[youma]

So I figured out what was the origin of my issue, I used vertcat in the begining of my code to extract all the sheets from the .xlsx, in order to have one table. Except that by doing that retime which is expecting a single table with a single time series gets instead arrays concatened vertically.

Anyhow, now I'm trying to see how could I instead of vertcat have a meteo_data table from the sheets :

meteo_table1 = readtable(filename, 'Sheet', 'Jan 2022');

meteo_table2 = readtable(filename, 'Sheet', 'Fev 2022');

meteo_table3 = readtable(filename, 'Sheet', 'Março 2022');

meteo_table4 = readtable(filename, 'Sheet', 'Abril 2022');

meteo_table5 = readtable(filename, 'Sheet', 'Maio 2022');

meteo_table6 = readtable(filename, 'Sheet', 'Junho 2022');

meteo_table7 = readtable(filename, 'Sheet', 'Julho 2022');

meteo_table8 = readtable(filename, 'Sheet', 'Agosto 2022');

meteo_table9 = readtable(filename, 'Sheet', 'Setembro 2022');

meteo_table10 = readtable(filename, 'Sheet', 'Outubro 2022');

meteo_table11 = readtable(filename, 'Sheet', 'Novembro 2022');

meteo_table12 = readtable(filename, 'Sheet', 'Dezembro 2022');

And I tried horzcat except but got this error : Error using tabular/horzcat Duplicate table variable name: 'TimeStamp'. So I checked apparently for each tables, I need each variables to be different, don't seem like an optimal solution, so if y'all happen to have something better in mind please feel free to share over here.

[youma]

@Star Strider feel free to take a look please, and give a buddy some leads.

Thanks for y'all time.

[Star Strider]

@youma — I’m lost. Perhaps using vertcat first then retime would work. The times should be consecutive, so I doubt that retime would have a problem with it.

I doubt that horizontal concatenation would work because of the nature of the data (assumed to be the same variables among the sheets) and that the times should be consecutive between the different sheets.

[youma]

@Star Strider Tat's exactly what I did, then I saw a similar topic, where they proposed to sort the TimeStamp, mine was already as I used vertcat, to be sure I sorted it anyway. But still got this error : " Incorrect number or types of inputs or outputs for function 'retime'.", so I doubted myself.

Any other possible explanation why it won't work? Cause it isn't working.

[Star Strider]

@youma — Note that retime only works on timetable arrays, not table arrays. That may be the problem.

[youma]

You're a life saver, you know that?

So yes, you rcomment made me wonder, cause I had a timetable in the beginning but while working with meteo_data I had to put it into this format'yyyy-mm-dd HH:MM:SS' at some point so I used "datestr", and forgot that I even used it.

Thanks again, it worked!!

[Star Strider]

@youma — My pleasure!