Extracting datetime and adding reference time

2 次查看（过去 30 天）
Luis Eduardo Cofré Lizama
编辑： Peter Perkins 2023-3-28
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

请先登录，再回答此问题。

回答（2 个）

Star Strider
Star Strider 2023-3-27
编辑：Star Strider 2023-3-27
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 个评论
显示 2更早的评论
Star Strider
Star Strider 2023-3-28
@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 2023-3-28
编辑：Peter Perkins 2023-3-28
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
Stephen23 2023-3-27
编辑：Stephen23 2023-3-27
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 个评论
Luis Eduardo Cofré Lizama
Luis Eduardo Cofré Lizama 2023-3-27
移动：Stephen23 2023-3-28
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 2023-3-28
编辑：Stephen23 2023-3-28
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

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Dates and Time 的更多信息

标签

产品


版本

R2022b

Community Treasure Hunt

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

Start Hunting!

Translated by