Extracting datetime and adding reference time

2 views (last 30 days)
Luis Eduardo Cofré Lizama
Edited: Peter Perkins on 28 Mar 2023
Hi All, I'm trying to extract the time and date from the highlighted cell below and then I need to add that (reference) to each of the timestamp (cell array) from row 10 onwards. When I just took a cell array from 10:end and used datetime, it added the current time.
In advance thanks for your help
Eduardo

Sign in to answer this question.

Answers (2)

Star Strider
Star Strider on 27 Mar 2023
Edited: Star Strider on 27 Mar 2023
Ran in:
This was a bit more involved than I theught it would be —
T1 = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/1337334/GPS_P01.txt', 'ReadVariableNames',0)
T1 = 51×3 table
Var1 Var2 Var3 ________________________________________________ ____ ____ {'# OpenField Export : 24/11/2022 8:07:02 AM' } NaN NaN {'# Reference time : 22/11/2022 9:05:10 PM UTC'} NaN NaN {'# CentisecTime : 166915111008' } NaN NaN {'# DeviceId : 27881' } NaN NaN {'# Speed Units : Meters Per Second' } NaN NaN {'# Distance Units : Meters' } NaN NaN {'# Period: "Activity 20221123072342"' } NaN NaN {'# Athlete: "1' } NaN NaN {'Timestamp' } NaN NaN {'05:10.1' } 0 0 {'05:10.2' } 0.1 0 {'05:10.3' } 0.2 0 {'05:10.4' } 0.3 0 {'05:10.5' } 0.4 0 {'05:10.6' } 0.5 0 {'05:10.7' } 0.6 0
Lv1 = cell2mat(cellfun(@(x)~isempty(strfind(x,'# Reference time :')), T1{:,1}, 'Unif',0));
RTs = extractAfter(T1{Lv1,1},': ');
RTdt = datetime(RTs, 'InputFormat','dd/MM/yyyy hh:mm:ss a ''UTC''', 'TimeZone','UTC', 'Format','dd/MM/yyyy HH:mm:ss');
idx = find(cell2mat(cellfun(@(x)~isempty(strfind(x,'Timestamp')), T1{:,1}, 'Unif',0)));
Extracted = T1(idx+1:end,:);
Timestamp = datetime(Extracted{:,1},'InputFormat','mm:ss.S', 'TimeZone','UTC', 'Format','mm.ss.S');
Timestamp = RTdt + timeofday(Timestamp);
Timestamp.Format = 'dd/MM/yyyy HH:mm:ss.S';
Extracted = removevars(Extracted,1);
Extracted = addvars(Extracted,Timestamp, 'Before',1)
Extracted = 42×3 table
Timestamp Var2 Var3 _____________________ ____ ____ 22/11/2022 21:10:20.1 0 0 22/11/2022 21:10:20.2 0.1 0 22/11/2022 21:10:20.3 0.2 0 22/11/2022 21:10:20.4 0.3 0 22/11/2022 21:10:20.5 0.4 0 22/11/2022 21:10:20.6 0.5 0 22/11/2022 21:10:20.7 0.6 0 22/11/2022 21:10:20.8 0.7 0 22/11/2022 21:10:20.9 0.8 0 22/11/2022 21:10:21.0 0.9 0 22/11/2022 21:10:21.1 1 0 22/11/2022 21:10:21.2 1.1 0 22/11/2022 21:10:21.3 1.2 0 22/11/2022 21:10:21.4 1.3 0 22/11/2022 21:10:21.5 1.4 0 22/11/2022 21:10:21.6 1.5 0
EDIT — (27 Mar 2023 at 18:52)
Changed ‘RTs’ and ‘RTdt’ slightly to be compatible with datetime and with the ‘UTC’ string embedded in it. Rest of the code unchanged.
.
  4 Comments
Show 2 older comments
Star Strider
Star Strider on 28 Mar 2023
@Peter Perkins — Thank you!
Out of curiosity, would this also solve the midnight rollover problem, or would my approach adding:
DI = cumsum([0; diff(hour(Timestamp))<0]); % Day Increment
Extracted.Timestamp + days(DI); % Add 1 Day Every Midnight
Extracted % Display Result
to my earlier code still be necessary?
.
Peter Perkins
Peter Perkins on 28 Mar 2023
Edited: Peter Perkins on 28 Mar 2023
First thing I always say is to remember that days is exactly 24hrs, while caldays is "one calendar day", which might be 23hrs, 24hrs, 24hrs+1sec, or 25hrs, depending on what time zone you are using, if any. In this case I think you do want days though.
But with duration I think everything just works, right? If you read the duration as a duration, it's a length of time, and adding that to a datetime does the right thing regardless of whether or not it crosses midnight. It should even work with durations longer than 24hrs. I confess that I did not fully understand where/if your code was going wrong at midnight, but if it was I suspect the culprit was reading the duration as a datetime.

Sign in to comment.

Stephen23
Stephen23 on 27 Mar 2023
Edited: Stephen23 on 27 Mar 2023
Ran in:
fnm = 'GPS_P01.txt';
txt = fileread(fnm);
one = regexp(txt,'(?<=REFERENCE\s*TIME\s*:\s*)[^\n]+','ignorecase','match','once');
ref = datetime(strtrim(one),'InputFormat','d/M/u h:m:s a z', 'TimeZone','utc', "Format","u-M-d H:m:s.S")
ref = datetime
2022-11-22 21:5:10.0
obj = detectImportOptions(fnm, "Range",9);
obj = setvartype(obj,"Timestamp","duration");
obj = setvaropts(obj,"Timestamp","InputFormat","mm:ss.S");
tbl = readtable(fnm, obj);
It is ambiguous if you want the minutes&seconds in the reference time to be incude or not, so here are both with and without them:
tbl.DT = tbl.Timestamp + dateshift(ref, "start","hour") % without
tbl = 42×4 table
Timestamp Seconds Velocity DT _________ _______ ________ ____________________ 05:10.1 0 0 2022-11-22 21:5:10.1 05:10.2 0.1 0 2022-11-22 21:5:10.2 05:10.3 0.2 0 2022-11-22 21:5:10.3 05:10.4 0.3 0 2022-11-22 21:5:10.4 05:10.5 0.4 0 2022-11-22 21:5:10.5 05:10.6 0.5 0 2022-11-22 21:5:10.6 05:10.7 0.6 0 2022-11-22 21:5:10.7 05:10.8 0.7 0 2022-11-22 21:5:10.8 05:10.9 0.8 0 2022-11-22 21:5:10.9 05:11.0 0.9 0 2022-11-22 21:5:11.0 05:11.1 1 0 2022-11-22 21:5:11.1 05:11.2 1.1 0 2022-11-22 21:5:11.2 05:11.3 1.2 0 2022-11-22 21:5:11.3 05:11.4 1.3 0 2022-11-22 21:5:11.4 05:11.5 1.4 0 2022-11-22 21:5:11.5 05:11.6 1.5 0 2022-11-22 21:5:11.6
tbl.DT = tbl.Timestamp + ref % with
tbl = 42×4 table
Timestamp Seconds Velocity DT _________ _______ ________ _____________________ 05:10.1 0 0 2022-11-22 21:10:20.1 05:10.2 0.1 0 2022-11-22 21:10:20.2 05:10.3 0.2 0 2022-11-22 21:10:20.3 05:10.4 0.3 0 2022-11-22 21:10:20.4 05:10.5 0.4 0 2022-11-22 21:10:20.5 05:10.6 0.5 0 2022-11-22 21:10:20.6 05:10.7 0.6 0 2022-11-22 21:10:20.7 05:10.8 0.7 0 2022-11-22 21:10:20.8 05:10.9 0.8 0 2022-11-22 21:10:20.9 05:11.0 0.9 0 2022-11-22 21:10:21.0 05:11.1 1 0 2022-11-22 21:10:21.1 05:11.2 1.1 0 2022-11-22 21:10:21.2 05:11.3 1.2 0 2022-11-22 21:10:21.3 05:11.4 1.3 0 2022-11-22 21:10:21.4 05:11.5 1.4 0 2022-11-22 21:10:21.5 05:11.6 1.5 0 2022-11-22 21:10:21.6
In the interest of keeping the original data (this is always useful for checking later), I would not remove the "TIMESTAMP" column/variable.
  2 Comments
Luis Eduardo Cofré Lizama
Luis Eduardo Cofré Lizama on 27 Mar 2023
Moved: Stephen23 on 28 Mar 2023
Thansk Stephen, it's working though there is a bit of an issue when in the original data the time passes the hour 59:59.9 nad becomes 00:00.0. Then 'cos the ref is added, it is as if the clock resetted to the ref value ans started again. I think at that point I need to add an hour, do you know how to do it?
Stephen23
Stephen23 on 28 Mar 2023
Edited: Stephen23 on 28 Mar 2023
Ran in:
" I think at that point I need to add an hour, do you know how to do it?"
fnm = 'GPS_P02.txt';
txt = fileread(fnm);
one = regexp(txt,'(?<=REFERENCE\s*TIME\s*:\s*)[^\n]+','ignorecase','match','once');
ref = datetime(strtrim(one),'InputFormat','d/M/u h:m:s a z', 'TimeZone','utc', "Format","u-M-d HH:mm:ss.S")
ref = datetime
2022-11-22 21:05:10.0
obj = detectImportOptions(fnm, "Range",9);
obj = setvartype(obj,"Timestamp","duration");
obj = setvaropts(obj,"Timestamp","InputFormat","mm:ss.S");
tbl = readtable(fnm, obj);
adj = hours(cumsum([false;diff(tbl.Timestamp)<0])); % add this line
tbl.DT = tbl.Timestamp + dateshift(ref, "start","hour") + adj % and term here
tbl = 42×4 table
Timestamp Seconds Velocity DT _________ _______ ________ _____________________ 05:10.1 0 0 2022-11-22 21:05:10.1 05:10.2 0.1 0 2022-11-22 21:05:10.2 05:10.3 0.2 0 2022-11-22 21:05:10.3 05:10.4 0.3 0 2022-11-22 21:05:10.4 05:10.5 0.4 0 2022-11-22 21:05:10.5 05:10.6 0.5 0 2022-11-22 21:05:10.6 05:10.7 0.6 0 2022-11-22 21:05:10.7 05:10.8 0.7 0 2022-11-22 21:05:10.8 05:10.9 0.8 0 2022-11-22 21:05:10.9 05:11.0 0.9 0 2022-11-22 21:05:11.0 59:59.1 1 0 2022-11-22 21:59:59.1 00:00.2 1.1 0 2022-11-22 22:00:00.2 00:00.3 1.2 0 2022-11-22 22:00:00.3 00:00.4 1.3 0 2022-11-22 22:00:00.4 00:00.5 1.4 0 2022-11-22 22:00:00.5 00:00.6 1.5 0 2022-11-22 22:00:00.6

Sign in to comment.

Categories

Find more on Calendar in Help Center and File Exchange

Tags

Products


Release

R2022b

Community Treasure Hunt

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

Start Hunting!