convert 12 hour data to 24 hour datetime data for timetable

61 views (last 30 days)
I have a data table with the first 3 variables corresponding to the date/time. I would like to convert the date time variables to
'yyy-MM-dd hh:mm:ss' data like I have for my other timetable. I'm just confused on how to efficiently convert this. In the images attached, the raw image is the table from my data txt file and desired is the desired timetable format.

Accepted Answer

Stephen23
Stephen23 on 16 Mar 2022
Edited: Stephen23 on 29 Apr 2022
Edit: read the comments to handle ambiguous midday/midnight:
MeasurementTime = datetime({'2015-12-18 08:03:05';'2015-12-18 10:03:17';'2015-12-18 12:03:13'});
Temp = [37.3;39.1;42.3];
Pressure = [30.1;30.03;29.9];
WindSpeed = [13.4;6.5;7.3];
AMPM = cellstr(['AM';'PM';'AM']);
TT = timetable(MeasurementTime,Temp,Pressure,WindSpeed,AMPM)
TT = 3×4 timetable
MeasurementTime Temp Pressure WindSpeed AMPM ____________________ ____ ________ _________ ______ 18-Dec-2015 08:03:05 37.3 30.1 13.4 {'AM'} 18-Dec-2015 10:03:17 39.1 30.03 6.5 {'PM'} 18-Dec-2015 12:03:13 42.3 29.9 7.3 {'AM'}
ix = strcmpi(TT.AMPM,'PM'); % avoids implicit ordering of FINDGROUPS
TT.MeasurementTime = TT.MeasurementTime + hours(12).*ix
TT = 3×4 timetable
MeasurementTime Temp Pressure WindSpeed AMPM ____________________ ____ ________ _________ ______ 18-Dec-2015 08:03:05 37.3 30.1 13.4 {'AM'} 18-Dec-2015 22:03:17 39.1 30.03 6.5 {'PM'} 18-Dec-2015 12:03:13 42.3 29.9 7.3 {'AM'}
  5 Comments
Poison Idea fan
Poison Idea fan on 29 Apr 2022
This works well. I am working on changing the file format but Labview is a big task on its own. Thanks for the help and time.

Sign in to comment.

More Answers (1)

Peter O
Peter O on 16 Mar 2022
Once imported, you could query the AM/PM column, and if the answer is PM, add 12 hours to the value, and then delete the AM/PM column from the table.
Borrowing the windspeed example table:
MeasurementTime = datetime({'2015-12-18 08:03:05';'2015-12-18 10:03:17';'2015-12-18 12:03:13'});
Temp = [37.3;39.1;42.3];
Pressure = [30.1;30.03;29.9];
WindSpeed = [13.4;6.5;7.3];
AMPM = cellstr(['AM';'PM';'AM']);
TT = timetable(MeasurementTime,Temp,Pressure,WindSpeed,AMPM)
TT = 3×4 timetable
MeasurementTime Temp Pressure WindSpeed AMPM ____________________ ____ ________ _________ ______ 18-Dec-2015 08:03:05 37.3 30.1 13.4 {'AM'} 18-Dec-2015 10:03:17 39.1 30.03 6.5 {'PM'} 18-Dec-2015 12:03:13 42.3 29.9 7.3 {'AM'}
G = findgroups(TT.AMPM) % Ordered alphabetically, so PM is G ==2
G = 3×1
1 2 1
TT.MeasurementTime(G==2,:) = TT.MeasurementTime(G==2,:) + hours(12)
TT = 3×4 timetable
MeasurementTime Temp Pressure WindSpeed AMPM ____________________ ____ ________ _________ ______ 18-Dec-2015 08:03:05 37.3 30.1 13.4 {'AM'} 18-Dec-2015 22:03:17 39.1 30.03 6.5 {'PM'} 18-Dec-2015 12:03:13 42.3 29.9 7.3 {'AM'}
TT.AMPM = [] % Drop AMPM field.
TT = 3×3 timetable
MeasurementTime Temp Pressure WindSpeed ____________________ ____ ________ _________ 18-Dec-2015 08:03:05 37.3 30.1 13.4 18-Dec-2015 22:03:17 39.1 30.03 6.5 18-Dec-2015 12:03:13 42.3 29.9 7.3
  1 Comment
Poison Idea fan
Poison Idea fan on 16 Mar 2022
Thank you for your comment. I figured that might be the case but I wasn't sure if there was a better way around it. Thank you.
Z

Sign in to comment.

Categories

Find more on Dates and Time in Help Center and File Exchange

Products


Release

R2021b

Community Treasure Hunt

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

Start Hunting!