How to extract specific dates from a datetime table?

Hi!

I have a datetime table (DATE1.mat) containing some dates from 1984 to 2022 at 10:00 am. I have another date timetable (DATE2.mat) that contains data from 2005 to 2019 at 10:00 am with temperature values. Can anyone please tell me how to find only the dates from DATE2.mat file that belong to DATE1.mat list?

Any feedback will be greatly appreciated!

Answer 1

[Les Beckham]

whos('-file', 'DATE1.mat')

  Name       Size            Bytes  Class       Attributes

  datet       -               6112  datetime              

whos('-file', 'DATE2.mat')

  Name      Size            Bytes  Class        Attributes

  B          -              83241  timetable              

load('DATE1.mat')
load('DATE2.mat')
whos

  Name           Size            Bytes  Class        Attributes

  B           5141x1             83241  timetable              
  ans            1x34               68  char                   
  cmdout         1x33               66  char                   
  datet        764x1              6112  datetime               

head(datet)

   02-May-1984 10:00:00
   03-Jun-1984 10:00:00
   19-Jun-1984 10:00:00
   23-Sep-1984 10:00:00
   09-Oct-1984 10:00:00
   25-Oct-1984 10:00:00
   10-Nov-1984 10:00:00
   26-Nov-1984 10:00:00

head(B)

            Time            Temp
    ____________________    ____

    01-Jan-2005 10:00:00    4.98
    02-Jan-2005 10:00:00    4.44
    03-Jan-2005 10:00:00    5.33
    04-Jan-2005 10:00:00    5.09
    05-Jan-2005 10:00:00    4.81
    06-Jan-2005 10:00:00     4.1
    07-Jan-2005 10:00:00    4.51
    08-Jan-2005 10:00:00    5.09

found_date_idx = ismember(datet, B.Time); % logical indexes for dates in datet that are found in B
B_selected = B(found_date_idx, :) % extract the rows from B that match the found dates

B_selected = 278×1 timetable
            Time            Temp
    ____________________    ____

    02-Feb-2006 10:00:00    5.58
    03-Feb-2006 10:00:00    5.39
    04-Feb-2006 10:00:00    5.44
    05-Feb-2006 10:00:00    5.92
    06-Feb-2006 10:00:00    5.54
    07-Feb-2006 10:00:00    5.26
    08-Feb-2006 10:00:00    4.96
    09-Feb-2006 10:00:00     3.7
    10-Feb-2006 10:00:00    3.69
    11-Feb-2006 10:00:00    3.55
    12-Feb-2006 10:00:00    3.21
    13-Feb-2006 10:00:00    3.93
    14-Feb-2006 10:00:00    4.74
    15-Feb-2006 10:00:00    4.87
    16-Feb-2006 10:00:00    4.73
    17-Feb-2006 10:00:00    5.69

Answer 2

[Eric Sofen]

If you know all the datetimes have 10:00:00 time components and you're just matching dates, timetable subscripting does that directly. Of course, if you're concerned about wanting non-exact matches (e.g. to match things that occur on the same date but not the same time), then the approach suggested by Adam is helpful. Other approaches for dealing with inexact matches may involve retime, timerange, or withtol.

load DATE1.mat
load DATE2.mat

B(datet,:)

ans = 278×1 timetable
            Time            Temp 
    ____________________    _____

    20-Jan-2005 10:00:00     2.75
    28-Jan-2005 10:00:00    -0.19
    05-Feb-2005 10:00:00     0.82
    13-Feb-2005 10:00:00     1.87
    09-Mar-2005 10:00:00     1.96
    17-Mar-2005 10:00:00     1.98
    10-Apr-2005 10:00:00     7.89
    18-Apr-2005 10:00:00     8.65
    26-Apr-2005 10:00:00     7.94
    12-May-2005 10:00:00    10.31
    28-May-2005 10:00:00    10.95
    05-Jun-2005 10:00:00    14.26
    15-Jul-2005 10:00:00    19.78
    23-Jul-2005 10:00:00    20.32
    08-Aug-2005 10:00:00    20.72
    16-Aug-2005 10:00:00     23.2

Answer 3

[Adam Danz]

Datetimes are rounded down to the start of the day using dateshift. Then, ismember finds matches between the two sets of dates.

load DATE1.mat  % datet (vector)
load DATE2.mat  % B  (table)

[isDateMatch, idx] = ismember(dateshift(datet,'start','day'),dateshift(B.Time,'start','day'));

B.Time(idx(isDateMatch)) corresponds with datet(isDateMatch)

This line verifies that they are equal.

isequal(dateshift(B.Time(idx(isDateMatch)),'start','day') , dateshift(datet(isDateMatch),'start','day'))