Extracting datetime and adding reference time

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

[Stephen23]

@Luis Eduardo Cofré Lizama: please upload the original data file by clicking the paperclip button.

Answer 1

[Star Strider]

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.

.

[Star Strider]

Adding a day at midnight is straightforward —

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);
DI = cumsum([0; diff(hour(Timestamp))<0]);                                  % Day Increment
Extracted.Timestamp + days(DI);                                             % Add ! Day Every Midnight 
Extracted                                                                   % Display Result

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  

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'

DateTime = 24×1 datetime array
   04-Jan-2023 23:59:59.873
   04-Jan-2023 23:59:59.893
   04-Jan-2023 23:59:59.933
   04-Jan-2023 23:59:59.963
   05-Jan-2023 00:00:00.003
   05-Jan-2023 00:00:00.043
   05-Jan-2023 00:00:00.073
   05-Jan-2023 00:00:00.103
   05-Jan-2023 23:59:59.873
   05-Jan-2023 23:59:59.893
   05-Jan-2023 23:59:59.933
   05-Jan-2023 23:59:59.963
   06-Jan-2023 00:00:00.003
   06-Jan-2023 00:00:00.043
   06-Jan-2023 00:00:00.073
   06-Jan-2023 00:00:00.103
   06-Jan-2023 23:59:59.873
   06-Jan-2023 23:59:59.893
   06-Jan-2023 23:59:59.933
   06-Jan-2023 23:59:59.963
   07-Jan-2023 00:00:00.003
   07-Jan-2023 00:00:00.043
   07-Jan-2023 00:00:00.073
   07-Jan-2023 00:00:00.103

.

[Peter Perkins]

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")

T = 42×3 table
    Timestamp    Seconds    Velocity
    _________    _______    ________

     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    
     05:10.8       0.7         0    
     05:10.9       0.8         0    
     05:11.0       0.9         0    
     05:11.1         1         0    
     05:11.2       1.1         0    
     05:11.3       1.2         0    
     05:11.4       1.3         0    
     05:11.5       1.4         0    
     05:11.6       1.5         0    

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)

hdr = 8×1 string array
    "# OpenField Export : 24/11/2022 8:07:02 AM"
    "# Reference time : 22/11/2022 9:05:10 PM UTC"
    "# CentisecTime : 166915111008"
    "# DeviceId : 27881"
    "# Speed Units : Meters Per Second"
    "# Distance Units : Meters"
    "# Period: "Activity 20221123072342""
    "# Athlete: "1"

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")

refTime = datetime
   22/11/2022 21:05:10.0

T.Timestamp = T.Timestamp + refTime;
TT = table2timetable(T)

TT = 42×2 timetable
          Timestamp          Seconds    Velocity
    _____________________    _______    ________

    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    

[Star Strider]

@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]

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.

Answer 2

[Stephen23]

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.

[Luis Eduardo Cofré Lizama]

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]

" 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