Extracting datetime and adding reference time
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
1 Comment
Stephen23
on 27 Mar 2023
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
Adding a day at midnight is straightforward —
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);
DI = cumsum([0; diff(hour(Timestamp))<0]); % Day Increment
Extracted.Timestamp + days(DI); % Add ! Day Every Midnight
Extracted % Display Result
The current data do not go past midnight, so I cannot demonstrate this with them, however I encourage you to see if this works with your full data set.
Using another dataset to illustrate this, it works, and should work similar to this with your data set as well —
% % Example —
Times = ['23:59:59:873'
'23:59:59:893'
'23:59:59:933'
'23:59:59:963'
'00:00:00:003'
'00:00:00:043'
'00:00:00:073'
'00:00:00:103'];
Time = datetime([Times; Times; Times], 'InputFormat','HH:mm:ss:SSS'); % Repeat To Test Code
DI = cumsum([0; diff(hour(Time))<0]); % Day Increment
Date = datetime('04-Jan-2023') + days(DI); % 'Date' Vector
DateTime = Date + timeofday(Time); % Date + Time
DateTime.Format = 'dd-MMM-yyyy HH:mm:ss.SSS'
.
Peter Perkins
on 28 Mar 2023
Edited: Peter Perkins
on 28 Mar 2023
Here's a slightly modernized version of SS's soln. Strings instead of cellstrs, and duration parsing in particular makes this simpler. And likely you want a timetable as the result:
T = readtable("https://www.mathworks.com/matlabcentral/answers/uploaded_files/1337334/GPS_P01.txt", NumHeaderLines=8,TextType="string");
T.Timestamp = duration(T.Timestamp,Format="mm:ss.S")
opts = delimitedTextImportOptions;
opts.DataLines = [1 8]; opts = setvaropts(opts,"Var1","Type","string");
hdr = readmatrix("https://www.mathworks.com/matlabcentral/answers/uploaded_files/1337334/GPS_P01.txt",opts)
refTime = extractAfter(hdr(contains(hdr,"Reference")),"# Reference time :");
refTime = datetime(refTime,InputFormat="dd/MM/yyyy hh:mm:ss a 'UTC'",TimeZone="UTC",Format="dd/MM/yyyy HH:mm:ss.S")
T.Timestamp = T.Timestamp + refTime;
TT = table2timetable(T)
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.
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
Luis Eduardo Cofré Lizama
on 27 Mar 2023
Moved: 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
Categories
Find more on Calendar in Help Center and File Exchange
on 27 Mar 2023
on 28 Mar 2023
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
