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

Question

Leo Rogers 2023-1-11

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1892430-add-dd-mmm-yyyy-data-to-datetime-array-in-form-hh-mm-ss-sss-with-rollover

评论： Star Strider 2023-4-12

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'

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Star Strider 2023-1-11

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1892430-add-dd-mmm-yyyy-data-to-datetime-array-in-form-hh-mm-ss-sss-with-rollover#answer_1146755

编辑：Star Strider 2023-1-12

在 MATLAB Online 中打开

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.

.

4 个评论
显示 2更早的评论隐藏 2更早的评论

dpb 2023-1-11

在 MATLAB Online 中打开

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 2023-1-11

编辑：Star Strider 2023-1-12

在 MATLAB Online 中打开

@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 2023-1-11

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1892430-add-dd-mmm-yyyy-data-to-datetime-array-in-form-hh-mm-ss-sss-with-rollover#answer_1146770

编辑：dpb 2023-1-11

在 MATLAB Online 中打开

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...

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

Answer 3

Leo Rogers 2023-1-12

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1892430-add-dd-mmm-yyyy-data-to-datetime-array-in-form-hh-mm-ss-sss-with-rollover#answer_1147295

在 MATLAB Online 中打开

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!

11 个评论
显示 9更早的评论隐藏 9更早的评论

youma 2023-4-5

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

dpb 2023-4-5

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 2023-4-12

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 2023-4-12

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 2023-4-12

@youma — My pleasure!

请先登录，再进行评论。

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

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

采纳的回答

4 个评论
显示 2更早的评论隐藏 2更早的评论

更多回答（2 个）

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

11 个评论
显示 9更早的评论隐藏 9更早的评论

另请参阅

类别

标签

Community Treasure Hunt

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

0 个评论 显示 -2更早的评论隐藏 -2更早的评论

采纳的回答

4 个评论 显示 2更早的评论隐藏 2更早的评论

更多回答（2 个）

0 个评论 显示 -2更早的评论隐藏 -2更早的评论

11 个评论 显示 9更早的评论隐藏 9更早的评论

另请参阅

类别

标签

Community Treasure Hunt

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

4 个评论
显示 2更早的评论隐藏 2更早的评论

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

11 个评论
显示 9更早的评论隐藏 9更早的评论