creating a look up table to compare datetime stamps

Question

Dharmesh Joshi 2022-11-20

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1857343-creating-a-look-up-table-to-compare-datetime-stamps

评论： Dharmesh Joshi 2022-11-24

Hi

I need to create a look up table, where i would need to specify. This will need to a manual table, where i would need to spicify the following:

Start DateTimeStamp, Stop DateTimeStamp and Device ID.

Then i would like to compare a incoming datastreams timestamp, aganist this lookup table to to NaN samples between the dates.

How can i create a table from Matlab, instead of using spreadsheet and how do you compare timestamps?

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Star Strider 2022-11-20

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1857343-creating-a-look-up-table-to-compare-datetime-stamps#answer_1106148

在 MATLAB Online 中打开

Start with the isbetween function, and add a separate logical vector for the device ID —

DateTime = datetime('now')+hours(0:4:150).';
DeviceID = randi(9, size(DateTime));
T1 = table(DateTime,DeviceID)
T1 = 38×2 table
DateTimeDeviceID____________________________

    20-Nov-2022 16:09:11       6    
    20-Nov-2022 20:09:11       3    
    21-Nov-2022 00:09:11       4    
    21-Nov-2022 04:09:11       8    
    21-Nov-2022 08:09:11       1    
    21-Nov-2022 12:09:11       4    
    21-Nov-2022 16:09:11       2    
    21-Nov-2022 20:09:11       8    
    22-Nov-2022 00:09:11       7    
    22-Nov-2022 04:09:11       7    
    22-Nov-2022 08:09:11       4    
    22-Nov-2022 12:09:11       5    
    22-Nov-2022 16:09:11       5    
    22-Nov-2022 20:09:11       5    
    23-Nov-2022 00:09:11       4    
    23-Nov-2022 04:09:11       8    
Start_DateTimeStamp = "21-Nov-2022 12:00";
Stop_DateTimeStamp = "22-Nov-2022 18:00";
Device_ID = randi(9)
Device_ID = 7
Lv = isbetween(T1.DateTime, Start_DateTimeStamp, Stop_DateTimeStamp) & Device_ID==T1.DeviceID;
Query = nnz(Lv)
Query = 2
Result = T1(Lv,:)
Result = 2×2 table
          DateTime          DeviceID
    ____________________    ________

    22-Nov-2022 00:09:11       7    
    22-Nov-2022 04:09:11       7    

Make appropriate changes to work with your data.

.

10 个评论
显示 8更早的评论隐藏 8更早的评论

Star Strider 2022-11-20

在 MATLAB Online 中打开

DateTime = datetime('now')+hours(0:4:250).';
DeviceID = randi(4, size(DateTime));
T1 = table(DateTime,DeviceID)                                           % Original Table
T1 = 63×2 table
DateTimeDeviceID____________________________

    20-Nov-2022 17:44:59       3    
    20-Nov-2022 21:44:59       3    
    21-Nov-2022 01:44:59       3    
    21-Nov-2022 05:44:59       2    
    21-Nov-2022 09:44:59       3    
    21-Nov-2022 13:44:59       4    
    21-Nov-2022 17:44:59       1    
    21-Nov-2022 21:44:59       2    
    22-Nov-2022 01:44:59       4    
    22-Nov-2022 05:44:59       4    
    22-Nov-2022 09:44:59       1    
    22-Nov-2022 13:44:59       1    
    22-Nov-2022 17:44:59       4    
    22-Nov-2022 21:44:59       2    
    23-Nov-2022 01:44:59       3    
    23-Nov-2022 05:44:59       2    
Start_DateTimeStamp = ["21-Nov-2022 12:00"; "23-Nov-2022 12:00"];
Stop_DateTimeStamp = ["22-Nov-2022 18:00"; "24-Nov-2022 18:00"];
Device_ID = randi(4)                                                    % Select 'Device ID'
Device_ID = 4
DTM = [datetime(Start_DateTimeStamp) datetime(Stop_DateTimeStamp)].'    % Each 'Start-Stop' Range Is A Separate Column
DTM = 2×2 datetime array
   21-Nov-2022 12:00:00   23-Nov-2022 12:00:00
   22-Nov-2022 18:00:00   24-Nov-2022 18:00:00
    
for k = 1:size(DTM,2)
    Lv(:,k) = isbetween(T1.DateTime, DTM(1,k), DTM(2,k)) & Device_ID==T1.DeviceID;
end
Query = nnz(Lv)                                                         % Optional, Displays Output Size 
Query = 6
for k = 1:size(Lv,2)
    Result{k,:} = T1(Lv(:,k),:);                                        % Create 'Result' Cell Array
end
for k = 1:numel(Result)
    DisplayResult = Result{k}
end
DisplayResult = 4×2 table
          DateTime          DeviceID
    ____________________    ________

    21-Nov-2022 13:44:59       4    
    22-Nov-2022 01:44:59       4    
    22-Nov-2022 05:44:59       4    
    22-Nov-2022 17:44:59       4    
DisplayResult = 2×2 table
          DateTime          DeviceID
    ____________________    ________

    23-Nov-2022 17:44:59       4    
    24-Nov-2022 01:44:59       4    
CatResult = cat(1,Result{:})                                            % Vertically Concatenate 'Result' Cell Array
CatResult = 6×2 table
          DateTime          DeviceID
    ____________________    ________

    21-Nov-2022 13:44:59       4    
    22-Nov-2022 01:44:59       4    
    22-Nov-2022 05:44:59       4    
    22-Nov-2022 17:44:59       4    
    23-Nov-2022 17:44:59       4    
    24-Nov-2022 01:44:59       4    

It may be necessary for you to modify this to work with your data, however this version of it appears to work correctly.

The ‘Result’ variable is a series (cell array) of individual tables.

The ‘CatResult’ variable vertically concatenates them into one table if you want that.

.

dpb 2022-11-20

"...using Matlab, i can create a table as Spreadsheet style sheet? Where i can manualy type my time range?"

If you're using MATLAB interactively, there's the "New Variable" or "Open Variable" functionality on the toolbar that does exactly that already built in. In 30 years of use with MATLAB I don't think I've ever wished for the facility nor have I used it "in anger" since the "workplace variables" feature was introduced probably 10 year ago or more; when one is an old dog used to working at command line, it's simpler to just write the code to create one or two temporary variables while fiddling around.

But, if one is doing real work that will be done more than once, then it makes sense to write code that does that repetitive piece of the job just given the data; to get data into such an animal as that it's generally more efficient to have the input data in another file that can be edited with any other toolset that is appropriate for the job. If you're building an app or a piece of code to do a given analysis, it's probably going to be more efficient to save the input data as text; the workspace variables created as above are transient to the session and will go away when you exit MATLAB unless you then write them out so may as well just write them in the file to begin with unless they truly are transient and something you'll never want/need again.

You've provided too little background on the application to have any really specific suggestions, but if you're dealing with some instrument or other process that is generating a realtime stream of data, then you probably could manage to tie the two together and have the input data refreshed periodically and prompt the user for the input ranges of interest...

dpb 2022-11-23

Nothing prevents you from organizing the file structure however it best suits the task...

Again, you've given us absolutely no context with which to work; how are we to have any idea of any specific organization or code structure that would fit?

Dharmesh Joshi 2022-11-24

Well i have sensors, such as tempeture,Humdity, motion etc , that are reporting data to our cloud servers as JSON. Then using API i am downloading that data onto Matlab.

But these sensors if they are placed away from the site, will produce false results, so the aim is to specify the timestamps and make the data NaN to avoid any confussion.

请先登录，再进行评论。