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 中查找有关 Calendar 的更多信息

标签

产品


版本

R2022b

Community Treasure Hunt

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

Start Hunting!

Translated by