creating a look up table to compare datetime stamps
Show older comments
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?
Accepted Answer
DateTime = datetime('now')+hours(0:4:150).';
DeviceID = randi(9, size(DateTime));
T1 = table(DateTime,DeviceID)
Start_DateTimeStamp = "21-Nov-2022 12:00";
Stop_DateTimeStamp = "22-Nov-2022 18:00";
Device_ID = randi(9)
Lv = isbetween(T1.DateTime, Start_DateTimeStamp, Stop_DateTimeStamp) & Device_ID==T1.DeviceID;
Query = nnz(Lv)
Result = T1(Lv,:)
Make appropriate changes to work with your data.
.
10 Comments
Dharmesh Joshi
on 20 Nov 2022
dpb
on 20 Nov 2022
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...
DateTime = datetime('now')+hours(0:4:250).';
DeviceID = randi(4, size(DateTime));
T1 = table(DateTime,DeviceID) % Original Table
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'
DTM = [datetime(Start_DateTimeStamp) datetime(Stop_DateTimeStamp)].' % Each 'Start-Stop' Range Is A Separate Column
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
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
CatResult = cat(1,Result{:}) % Vertically Concatenate 'Result' Cell Array
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
on 20 Nov 2022
Star Strider
on 20 Nov 2022
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.
dpb
on 20 Nov 2022
"...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
on 23 Nov 2022
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
.
dpb
on 23 Nov 2022
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
on 24 Nov 2022
More Answers (1)
dpb
on 20 Nov 2022
0 votes
Categories
Find more on Tables in Help Center and File Exchange
on 20 Nov 2022
on 24 Nov 2022
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
