Split table from database into sub-tables dependent from timestamp difference
4 views (last 30 days)
Show older comments
Ferdinand Grosse-Dunker
on 24 Jul 2019
Commented: Ferdinand Grosse-Dunker
on 31 Jul 2019
Hi there,
I'm importing measurement data from a PostgreSQL database into one Matlab table, called data:
%% Make connection to database
conn = database('PostgreSQL','NameOfDatabase','Password');
%Set query to execute on the database
query = ['SELECT * ' ...
'FROM postgres.public.data'];
%% Execute query and fetch results
data = fetch(conn,query);
the data table looks like this:
timestamp ID x y z
'2019-07-22 15:46:24.919' 41034 32.5437012000000 6.59714985000000 0
'2019-07-22 15:46:25.019' 41034 32.5466003000000 6.59952021000000 0
'2019-07-22 15:46:25.119' 41034 32.5657997000000 6.60097980000000 0
'2019-07-22 15:46:25.219' 41034 32.5840988000000 6.59719992000000 0
'2019-07-22 15:46:25.319' 41034 32.5975990000000 6.59470987000000 0
'2019-07-22 15:46:25.419' 41034 32.6068001000000 6.59368992000000 0
'2019-07-22 15:46:25.519' 41034 32.6091003000000 6.59696007000000 0
'2019-07-22 15:46:25.619' 41034 32.6016006000000 6.59864998000000 0
'2019-07-22 15:46:25.719' 41034 32.5957985000000 6.59865999000000 0
Now I want to create a new table for each measurement. I can only distinguish the next measurement from a time difference (of a few seconds) in the timestamp column. This is somehow what I'm trying:
format long
data.timestamp = categorical(data.timestamp);
Difference = caldiff(data.timestamp);
Tablename = ('Mess' + 'Counter');
Counter = 0;
for each Difference > 2 seconds
% create new table (Tablename)
% insertrows from start to difference row
Counter = Counter + 1;
end
Is caldiff the right funktion for the very small time differences?
Thanks
1 Comment
Stephen23
on 24 Jul 2019
"Now I want to create a new table for each measurement"
Why?
MATLAB tables are designed to make it easy to group data and apply operations on those groups:
https://www.mathworks.com/help/matlab/matlab_prog/split-table-data-variables-and-apply-functions.html
In contrast, splitting up data like that usually just makes it harder to work with.
What is your actual goal? How do you wish to process your data?
Accepted Answer
Jon
on 24 Jul 2019
Edited: Jon
on 24 Jul 2019
First I would recommend making a new column in your table with the time expressed as a MATLAB datetime array, for example you could use the following, (see https://www.mathworks.com/help/matlab/matlab_prog/set-display-format-of-date-and-time-arrays.html#buhb8v6-1 for datetime format conversion)
data.time = datetime(data.timestamp,'InputFormat','yyyy-MM-dd HH:mm:ss.SSS')
Then following up on Stephens comment, you really don't want to split this up into lots of small tables, just add the information you want to this one. So for example you could assign a column called measurement which assigns values 1,2,3 ... according to the time range that the measurements occur.
So for example
% calculate elapsed time in seconds from start of test
% (assumes you have already added the time column as a datetime array
et = seconds(data.time - data.time(1));
% make new column with measurement number, with a new measurement starting every 2 seconds
data.measurement = ceil(et/2)
Now if you want to retrieve some specific measurements, for example the x values for measurement number 3 can just use
x = data.x(data.measurement==3)
I recommend you look at https://www.mathworks.com/help/matlab/matlab_prog/compute-elapsed-time.html and https://www.mathworks.com/help/matlab/matlab_prog/compute-elapsed-time.html for more info about working with datetime arrays
14 Comments
Jon
on 31 Jul 2019
I don't think it is necessary to use the findgroups function for this purpose. Assuming you now have your data in a table called data, with columns named time, x, y, and measurement. You can simply use
plot(data.x(data.measurement==5),data.y(data.measurement==5))
or perhaps to make the code a little more readable and efficient (avoid finding the matching columns twice) you could use
% make logical vector whose elements are set to true on rows with the desired
% measurement number
idl = data.measurement==5;
% plot the x and y values corresponding to the desired measurement using logical indexing
plot(data.x(idl),data.y(idl))
More Answers (0)
See Also
Categories
Find more on String in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!