creating a look up table to compare datetime stamps

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?

Answer 1

[Star Strider]

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
          DateTime          DeviceID
    ____________________    ________

    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.

.

[Dharmesh Joshi]

Thanks,

Isbetween seems to be good.

But i need to make a list of multiple start and stop dates, ideally kept in a table. How do i do that?

[dpb]

Same way as @Star Strider illustrated with the dates -- from whence ever you get those dates/times, make the table from them.

isbetween is vectorized to accept a vector start/stop times so it's all already built for you to use...just have at it; no need to wait on anybody here...

[Star Strider]

DateTime = datetime('now')+hours(0:4:250).';
DeviceID = randi(4, size(DateTime));

T1 = table(DateTime,DeviceID)                                           % Original Table

T1 = 63×2 table
          DateTime          DeviceID
    ____________________    ________

    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.

.

[Dharmesh Joshi]

Thanks

I will give that a try.

Is it possible using Matlab, i can create a table as Spreadsheet style sheet? Where i can manualy type my time range? Or does it have to be coded?

[Star Strider]

My pleasure!

You could write it in Excel or a text file and then read it.

Regardless of how it started (for example two separate columns), it would have to be in the format of the current ‘DTM’ matrix for it to work with my code. That may mean simply transpoising it, so if it began as ‘n’ rows by 2 columns with each row being a separate start-stop interval, it would need to be transposed to a 2 row by ‘n’ column ‘DTM’ matrix.

The entries would have to be string arrays (such as I coded them) to work with isbetween.

[dpb]

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

[Dharmesh Joshi]

I got it working, but instead of keeping the start,stop time stamps along with device id in the same file as the source could it would be prefered if it was in seperate file.

[Star Strider]

As always, my pleasure!

The ‘DisplayResult’ outputs are themselves tables, so just use writetable to write them to separate files.

Give them appropriate file names. If the file names include numbers, zero-pad them to make them easier to locate and load later, for example using:

Nr = [1 10 50 100];
for k = 1:numel(Nr)
    outfilename = sprintf('Result %03d.txt',Nr(k))
end

outfilename = 'Result 001.txt'

outfilename = 'Result 010.txt'

outfilename = 'Result 050.txt'

outfilename = 'Result 100.txt'

That way, they will be sorted correctly. Change the format string in the sprintf call as necessary.

.

[dpb]

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]

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.

Answer 2

[dpb]

Read all about <MATLAB timetable> here. It has all the tools ready-built for you...