Extracting datetime and adding reference time
4 views (last 30 days)
Show older comments
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
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/1337139/image.png)
Answers (2)
Star Strider
on 27 Mar 2023
Edited: Star Strider
on 27 Mar 2023
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)
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)
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
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
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.
Stephen23
on 27 Mar 2023
Edited: Stephen23
on 27 Mar 2023
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")
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.DT = tbl.Timestamp + ref % with
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
Stephen23
on 28 Mar 2023
Edited: Stephen23
on 28 Mar 2023
" 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")
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
See Also
Categories
Find more on Dates and Time 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!