Extracting time valued between lower and upper bound

2022 9 13
1 个回答
回答已采纳
更新时间：2022 9 17
6 次查看（30 天）

0 个投票

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
dpb 2022-9-13

0 个投票

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

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

Yasir Ali
Yasir Ali 2022-9-14
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
dpb 2022-9-14
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
Yasir Ali 2022-9-14
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
dpb 2022-9-14
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.
dpb
dpb 2022-9-17
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...

请先登录，再进行评论。

更多回答（0 个）

类别

在 帮助中心 和 File Exchange 中查找有关 Logical 的更多信息

产品

版本

R2022a

标签

Community Treasure Hunt

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

Start Hunting!

Translated by