How can I compare a datetime array with a timetable to delete all not existing days in it?

7 views (last 30 days)
I have a datetime array for some days of the year and a timetable with 24 hours a day (365 days, i.e. 8760 lines). I want to delete all lines in the timetable that are not in my datetime array. Which commands can I use? The timetable has values like temperature.
01/01/2000
01/02/2000
01/04/2000
01/06/2000
01/08/2000
01/20/2000
...
01/01/2000 00:00:00
01/01/2000 01:00:00
01/01/2000 02:00:00
01/01/2000 03:00:00
01/01/2000 04:00:00
01/01/2000 05:00:00
01/01/2000 06:00:00
01/01/2000 07:00:00
01/01/2000 08:00:00
01/01/2000 09:00:00
...

Accepted Answer

Adam Danz
Adam Danz on 20 Jan 2021
Edited: Adam Danz on 20 Jan 2021
To match dates while ignoring time, use ismember() along with dateshift() to ignore time.
Demo:
  • TT is a timetable with datetime values stored in TT.Time.
  • dateList is a vector of selected datetime values that indicate which days in TT should be accepted. All other rows of TT are removed.
% Create timetable
Time = datetime(2000,1,1,1,0,0)+days(0:30)'+hours(0:30)';
TT = timetable(Time, rand(size(Time)));
head(TT)
ans = 8x1 timetable
Time Var1 ____________________ _______ 01-Jan-2000 01:00:00 0.91812 02-Jan-2000 02:00:00 0.89075 03-Jan-2000 03:00:00 0.26125 04-Jan-2000 04:00:00 0.60428 05-Jan-2000 05:00:00 0.66809 06-Jan-2000 06:00:00 0.62913 07-Jan-2000 07:00:00 0.18667 08-Jan-2000 08:00:00 0.55318
% List of accepted dates
dateList = datetime(2000,1,1)+days([0 1 3 5 7 19])'
dateList = 6×1 datetime array
01-Jan-2000 02-Jan-2000 04-Jan-2000 06-Jan-2000 08-Jan-2000 20-Jan-2000
% Identify and remove rows of TT with dates outside of dateList, ignoring time.
rmIdx = ~ismember(dateshift(TT.Time,'Start','Day'), dateshift(dateList,'Start','Day'));
TT(rmIdx,:) = []
TT = 6x1 timetable
Time Var1 ____________________ _______ 01-Jan-2000 01:00:00 0.91812 02-Jan-2000 02:00:00 0.89075 04-Jan-2000 04:00:00 0.60428 06-Jan-2000 06:00:00 0.62913 08-Jan-2000 08:00:00 0.55318 20-Jan-2000 20:00:00 0.6004
  12 Comments
Alexander Koch
Alexander Koch on 21 Jan 2021
Edited: Alexander Koch on 21 Jan 2021
@Adam Danz First of all, Thank You!! But...
If I would like to convert '31-Jan-2000' to '01/31/2000' Matlab uses mmm for Jan and mm for day 31. I can't understand. So if I'm trying to add one day to '01/31/2000' ('MM/dd/yyyy') Matlab calculates something like '01/01/2000' and with HH:mm:ss you can see that 'mm' means month and 'MM' minutes.... It's reversed.
S1 = '31-Jan-2000';
I = datenum(S1, 'dd-mmm-yyyy');
S2 = datestr(I, 'mm/dd/yyyy');
I'm sorry, but I don't get it if it's mm or MM for month and minutes and in which case :O
Edit:
documentation for datetime formats: The letter identifiers that datetime accepts are different from the identifiers used by the datestr, datenum, and datevec functions.
Now it works, if I'm using mm for Month in datenum... confusing.
Adam Danz
Adam Danz on 21 Jan 2021
Edited: Adam Danz on 21 Jan 2021
@Alexander Koch look at the documentation for datestr datevec and datenum. Unfortunately their format notation is not consistent with datetime. For those functions months use lowercase m. But your data in the mat file are using datetime which uses uppercase M for month. It's a very unfortunate inconsistency that tricks a lot of people. But the lesson is always to refer to the documentation when you're having these kinds of problems.

Sign in to comment.

More Answers (0)

Categories

Find more on Dates and Time in Help Center and File Exchange

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!