Extracting time valued between lower and upper bound

Hi Everyone,

I am trying to extract all the rows corresponding to lower and upper bound times. I have a big table (28125x31) and minimum and maximum times in table are 06:13:19 AM and 06:26:37 AM, respectively. I want to get data between t_min and t_max, whereas t_min = 06:16:44 AM and t_max = 06:17:05 AM. I am using following code

row = a([isbetween(a.time(:),t_min,t_max)],:);

where, a is table (28125x31), a.time is vector containing times as shown in attached excel sheet, t_min & t_max are described previously. While this bit of code, I am getting row table as 21871x31. When I check min and max time in row.time, they are 06:16:45 AM and 06:26:37 AM. The upper bound in row.time should be equal to 06:17:05 AM, but somehow code is giving me this value, which leads to inapporporaite size of table.

I have attached a .mat file (Data), containing a table file, which is named as "a".

Any help in figuring out the issue and how to get around it would be highly appreciated. Thank you.

[dpb]

You forgot to show us the code definitions for t_min, t_max...

Answer 1

[dpb]

>> tmp=a(1:10,[end-2:end])              % look at a sample of the pertinent data
ans =
  10×3 table
         datetime             date           time    
    ___________________    ___________    ___________
    2019-08-19 20:13:19    20-Aug-2019    06:13:19 AM
    2019-08-19 20:13:19    20-Aug-2019    06:13:19 AM
    2019-08-19 20:13:19    20-Aug-2019    06:13:19 AM
    2019-08-19 20:13:19    20-Aug-2019    06:13:19 AM
    2019-08-19 20:13:19    20-Aug-2019    06:13:19 AM
    2019-08-19 20:13:20    20-Aug-2019    06:13:20 AM
    2019-08-19 20:13:20    20-Aug-2019    06:13:20 AM
    2019-08-19 20:13:19    20-Aug-2019    06:13:19 AM
    2019-08-19 20:13:19    20-Aug-2019    06:13:19 AM
    2019-08-19 20:13:19    20-Aug-2019    06:13:19 AM
>> tmp.time.Format='Default'            % see what the time data really are...
tmp =
  10×3 table
           datetime               date                time        
    _______________________    ___________    ____________________
    19-Aug-2019 08:13:19 PM    20-Aug-2019    20-Aug-2019 06:13:19
    19-Aug-2019 08:13:19 PM    20-Aug-2019    20-Aug-2019 06:13:19
    19-Aug-2019 08:13:19 PM    20-Aug-2019    20-Aug-2019 06:13:19
    19-Aug-2019 08:13:19 PM    20-Aug-2019    20-Aug-2019 06:13:19
    19-Aug-2019 08:13:19 PM    20-Aug-2019    20-Aug-2019 06:13:19
    19-Aug-2019 08:13:20 PM    20-Aug-2019    20-Aug-2019 06:13:20
    19-Aug-2019 08:13:20 PM    20-Aug-2019    20-Aug-2019 06:13:20
    19-Aug-2019 08:13:19 PM    20-Aug-2019    20-Aug-2019 06:13:19
    19-Aug-2019 08:13:19 PM    20-Aug-2019    20-Aug-2019 06:13:19
    19-Aug-2019 08:13:19 PM    20-Aug-2019    20-Aug-2019 06:13:19
>>

NOTA BENE: despite it not being displayed, the time variable is still a datetime and a datetime CANNOT EXIST without a date as a time alone.

It's also interesting that the times seem to flip back and forth between 19 and 20 seconds rather than being monotonic....is this intended???

Look at the end of the table -- NOTA BENE there that there seems to be a problem on the last entry being same as first time element???

>> a(end-10+[1:10],[end-2:end])
ans =
  10×3 table
         datetime             date           time    
    ___________________    ___________    ___________
    2019-08-19 20:26:36    20-Aug-2019    06:26:36 AM
    2019-08-19 20:26:37    20-Aug-2019    06:26:37 AM
    2019-08-19 20:26:37    20-Aug-2019    06:26:37 AM
    2019-08-19 20:26:37    20-Aug-2019    06:26:37 AM
    2019-08-19 20:26:37    20-Aug-2019    06:26:37 AM
    2019-08-19 20:26:37    20-Aug-2019    06:26:37 AM
    2019-08-19 20:26:37    20-Aug-2019    06:26:37 AM
    2019-08-19 20:26:37    20-Aug-2019    06:26:37 AM
    2019-08-19 20:26:37    20-Aug-2019    06:26:37 AM
    2019-08-19 20:13:19    20-Aug-2019    06:13:19 AM
>>

Now select between the times as stored; must use a datetime to match the actual values stored...

>> ix=isbetween(a.time,datetime(2019,8,20,6,16,44),datetime(2019,8,20,6,17,05));
>> sum(ix)
ans =
   898
>> 

To eliminate the date, use

>> tmp.TOD=timeofday(tmp.time)
tmp =
  10×4 table
           datetime               date                time              TOD   
    _______________________    ___________    ____________________    ________
    19-Aug-2019 08:13:19 PM    20-Aug-2019    20-Aug-2019 06:13:19    06:13:19
    19-Aug-2019 08:13:19 PM    20-Aug-2019    20-Aug-2019 06:13:19    06:13:19
    19-Aug-2019 08:13:19 PM    20-Aug-2019    20-Aug-2019 06:13:19    06:13:19
    19-Aug-2019 08:13:19 PM    20-Aug-2019    20-Aug-2019 06:13:19    06:13:19
    19-Aug-2019 08:13:19 PM    20-Aug-2019    20-Aug-2019 06:13:19    06:13:19
    19-Aug-2019 08:13:20 PM    20-Aug-2019    20-Aug-2019 06:13:20    06:13:20
    19-Aug-2019 08:13:20 PM    20-Aug-2019    20-Aug-2019 06:13:20    06:13:20
    19-Aug-2019 08:13:19 PM    20-Aug-2019    20-Aug-2019 06:13:19    06:13:19
    19-Aug-2019 08:13:19 PM    20-Aug-2019    20-Aug-2019 06:13:19    06:13:19
    19-Aug-2019 08:13:19 PM    20-Aug-2019    20-Aug-2019 06:13:19    06:13:19
>> tmp.TOD
ans = 
  10×1 duration array
   06:13:19
   06:13:19
   06:13:19
   06:13:19
   06:13:19
   06:13:20
   06:13:20
   06:13:19
   06:13:19
   06:13:19
>> 

which you see is now a duration of time since midnight of the given day. This works for a given day; if there are multiple days in a time vector, then it will have duplicate values for the same time-of-day of different days.

[Yasir Ali]

Thank you @dpb for your detalied response. To answer your question from where t_min and t_max are coming, they are coming from another table, where i sequentially get values corresponding to unique id. That being said, from your code, I can see how did you provide input to "isbetween" function. With t_min & t_max being in time like 06:16:44 AM or using datetime, how did you get datetime in format like this "datetime(2019,8,20,6,16,44)"? Any function to change datetime from existing format to this? Thank you once again for your help.

[dpb]

See the <datetime doc> for all the input forms; that's the numeric one of (year,mon,day,hr,min,sec) -- this is input TO the datetime function and returns a datetime corresponding to that specific date. NB: as above states, a datetime variable does not exist as a time alone, it must contain a date and the comparison done by isbetween includes the absolute time in comparison of the ranges looked for with respect to the array looked into.

IOW, you will have to ensure that the t_min,t_max values are actually within the dates of the array you're trying to locate the times in -- if that other table doesn't have a corresponding day in it that matches, then the result won't be that expected.

[Yasir Ali]

Hi @dpb, i am struggling to get proper values. I converted existing datetime column vector using "datevec" function and gave input to "isbetween" for lower and upper bound times, but it getting the sum(ix) = 21871, rather than 898. In your code, you write t_min and t_max manually, while i am converting them using "datevec", but it seems to be not working for me.

Although a big ask, by any chance can you just guide me through this hurdle via a quick/short zoom call? Thank you.

[dpb]

You've neglected to provide the pivotal piece of information that is needed -- a sample of the data of this other table and the code you used to create those limits -- all we have is what you post and you've not provided the key information except by verbal description.

Build a (short) example case that includes sufficient data to run it that illustrates the problem -- you don't need but a 10 or-so-length time vector with values from the minimum to maximum and some in between the wanted search range to illustrate; code logic is immaterial of data sizes(*).

Almost certainly, the issue you're having is related to the correct date between the two vectors, but we need to see all the gory details of what you did and the actual data to be able to tell just what went wrong.

(*) Of course, your count of number found will now be 6 or whatever based on the data, not in the hundreds/thousands, but it's immaterial how many, exactly. And debugging with a small sample size makes it a lot easier to be able to inspect what actually did get, so there's an advantage there, besides.

[Yasir Ali]

Thanks @dpb, it helped and worked.

[dpb]

Great!

It's amazing how often trying to produce a small example that reproduces a problem will uncover the issue and actually solve the problem itself in doing so...