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'
채택된 답변
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'
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
Leo Rogers
2023년 1월 11일
dpb
2023년 1월 11일
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.
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'
What afraid of, @Star Strider, the test for ~0 turns over a day for every hour change, not just rollover at midnight...
@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]
EDIT — (12 Jan 2023 00:40)
Added example.
.
추가 답변 (2개)
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'
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...
Leo Rogers
2023년 1월 12일
댓글 수: 11
Star Strider
2023년 1월 12일
As always, my pleasure!
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
end
dpb
2023년 4월 5일
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일
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
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일
@Star Strider feel free to take a look please, and give a buddy some leads.
Thanks for y'all time.
Star Strider
2023년 4월 12일
@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.
@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
2023년 4월 12일
@youma — Note that retime only works on timetable arrays, not table arrays. That may be the problem.
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!
카테고리
도움말 센터 및 File Exchange에서 Timetables에 대해 자세히 알아보기
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
Translated by 
