Clear Filters
Clear Filters

convert 12 hour data to 24 hour datetime data for timetable

44 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
Stephen23
Stephen23 on 29 Apr 2022
Edited: Stephen23 on 29 Apr 2022
"Maybe there is a better way to import the data file that can avoid this issue in the first place." I doubt that there is a simple way to import such a mixed-up datestamp as that: the units are totally out of order. We can import the time-of-day part as DURATION, the AM/PM part as text, and the date as DATETIME, for example:
fnm = 'CRD445_20220427.txt';
opt = detectImportOptions(fnm);
opt.VariableNames = {'time','AP','date','val'};
opt = setvaropts(opt,'date','InputFormat','MM/dd/u');
tbl = readtable('CRD445_20220427.txt',opt)
tbl = 90207×4 table
time AP date val ____________ ______ __________ __________ 01:39:24.908 {'PM'} 04/27/2022 2.311e-05 01:39:25.735 {'PM'} 04/27/2022 2.3087e-05 01:39:26.730 {'PM'} 04/27/2022 2.2999e-05 01:39:27.792 {'PM'} 04/27/2022 2.2986e-05 01:39:28.821 {'PM'} 04/27/2022 2.2813e-05 01:39:29.810 {'PM'} 04/27/2022 2.2812e-05 01:39:30.803 {'PM'} 04/27/2022 2.2675e-05 01:39:31.839 {'PM'} 04/27/2022 2.2627e-05 01:39:32.752 {'PM'} 04/27/2022 2.2544e-05 01:39:33.797 {'PM'} 04/27/2022 2.2387e-05 01:39:34.801 {'PM'} 04/27/2022 2.2405e-05 01:39:35.792 {'PM'} 04/27/2022 2.236e-05 01:39:36.792 {'PM'} 04/27/2022 2.2344e-05 01:39:37.800 {'PM'} 04/27/2022 2.2123e-05 01:39:38.788 {'PM'} 04/27/2022 2.2098e-05 01:39:39.787 {'PM'} 04/27/2022 2.2052e-05
And then make the adjustment for AM/PM, just as I showed before:
hh = hms(tbl.time);
ip = strcmpi(tbl.AP,'PM');
ix = (ip & hh<12)-(~ip & hh==12);
tmp = tbl.date + tbl.time + hours(12).*ix;
tmp.Format = 'yyyy-MM-dd HH:mm:ss.SSS';
tbl.dt = tmp
tbl = 90207×5 table
time AP date val dt ____________ ______ __________ __________ _______________________ 01:39:24.908 {'PM'} 04/27/2022 2.311e-05 2022-04-27 13:39:24.908 01:39:25.735 {'PM'} 04/27/2022 2.3087e-05 2022-04-27 13:39:25.735 01:39:26.730 {'PM'} 04/27/2022 2.2999e-05 2022-04-27 13:39:26.730 01:39:27.792 {'PM'} 04/27/2022 2.2986e-05 2022-04-27 13:39:27.792 01:39:28.821 {'PM'} 04/27/2022 2.2813e-05 2022-04-27 13:39:28.821 01:39:29.810 {'PM'} 04/27/2022 2.2812e-05 2022-04-27 13:39:29.810 01:39:30.803 {'PM'} 04/27/2022 2.2675e-05 2022-04-27 13:39:30.803 01:39:31.839 {'PM'} 04/27/2022 2.2627e-05 2022-04-27 13:39:31.839 01:39:32.752 {'PM'} 04/27/2022 2.2544e-05 2022-04-27 13:39:32.752 01:39:33.797 {'PM'} 04/27/2022 2.2387e-05 2022-04-27 13:39:33.797 01:39:34.801 {'PM'} 04/27/2022 2.2405e-05 2022-04-27 13:39:34.801 01:39:35.792 {'PM'} 04/27/2022 2.236e-05 2022-04-27 13:39:35.792 01:39:36.792 {'PM'} 04/27/2022 2.2344e-05 2022-04-27 13:39:36.792 01:39:37.800 {'PM'} 04/27/2022 2.2123e-05 2022-04-27 13:39:37.800 01:39:38.788 {'PM'} 04/27/2022 2.2098e-05 2022-04-27 13:39:38.788 01:39:39.787 {'PM'} 04/27/2022 2.2052e-05 2022-04-27 13:39:39.787
Now lets check the DATETIMEs. Lets have a closer look at the rows around midnight:
[~,idx] = min(abs(tbl.dt-datetime(2022,4,28,0,0,0)))
idx = 37226
tbl(idx-4:idx+4,:)
ans = 9×5 table
time AP date val dt ____________ ______ __________ __________ _______________________ 11:59:55.856 {'PM'} 04/27/2022 1.815e-05 2022-04-27 23:59:55.856 11:59:56.849 {'PM'} 04/27/2022 1.8109e-05 2022-04-27 23:59:56.849 11:59:57.856 {'PM'} 04/27/2022 1.7975e-05 2022-04-27 23:59:57.856 11:59:58.858 {'PM'} 04/27/2022 1.7957e-05 2022-04-27 23:59:58.858 11:59:59.850 {'PM'} 04/27/2022 1.7965e-05 2022-04-27 23:59:59.850 12:00:00.767 {'AM'} 04/28/2022 1.8077e-05 2022-04-28 00:00:00.767 12:00:01.828 {'AM'} 04/28/2022 1.8117e-05 2022-04-28 00:00:01.828 12:00:02.839 {'AM'} 04/28/2022 1.8175e-05 2022-04-28 00:00:02.839 12:00:03.851 {'AM'} 04/28/2022 1.8147e-05 2022-04-28 00:00:03.851
and around midday:
[~,idx] = min(abs(tbl.dt-datetime(2022,4,28,12,0,0)))
idx = 80418
tbl(idx-4:idx+4,:)
ans = 9×5 table
time AP date val dt ____________ ______ __________ __________ _______________________ 11:59:56.078 {'AM'} 04/28/2022 2.0804e-05 2022-04-28 11:59:56.078 11:59:57.047 {'AM'} 04/28/2022 2.0744e-05 2022-04-28 11:59:57.047 11:59:58.075 {'AM'} 04/28/2022 2.0697e-05 2022-04-28 11:59:58.075 11:59:59.033 {'AM'} 04/28/2022 2.0764e-05 2022-04-28 11:59:59.033 12:00:00.043 {'PM'} 04/28/2022 2.0705e-05 2022-04-28 12:00:00.043 12:00:01.033 {'PM'} 04/28/2022 2.0727e-05 2022-04-28 12:00:01.033 12:00:02.051 {'PM'} 04/28/2022 2.0736e-05 2022-04-28 12:00:02.051 12:00:03.051 {'PM'} 04/28/2022 2.0717e-05 2022-04-28 12:00:03.051 12:00:04.050 {'PM'} 04/28/2022 2.0659e-05 2022-04-28 12:00:04.050
and also around 13:00:
[~,idx] = min(abs(tbl.dt-datetime(2022,4,28,13,0,0)))
idx = 84017
tbl(idx-4:idx+4,:)
ans = 9×5 table
time AP date val dt ____________ ______ __________ __________ _______________________ 12:59:55.680 {'PM'} 04/28/2022 2.0893e-05 2022-04-28 12:59:55.680 12:59:56.670 {'PM'} 04/28/2022 2.0909e-05 2022-04-28 12:59:56.670 12:59:57.684 {'PM'} 04/28/2022 2.0897e-05 2022-04-28 12:59:57.684 12:59:58.686 {'PM'} 04/28/2022 2.0916e-05 2022-04-28 12:59:58.686 12:59:59.681 {'PM'} 04/28/2022 2.0845e-05 2022-04-28 12:59:59.681 01:00:00.671 {'PM'} 04/28/2022 2.0923e-05 2022-04-28 13:00:00.671 01:00:01.675 {'PM'} 04/28/2022 2.0932e-05 2022-04-28 13:00:01.675 01:00:02.675 {'PM'} 04/28/2022 2.093e-05 2022-04-28 13:00:02.675 01:00:03.674 {'PM'} 04/28/2022 2.0888e-05 2022-04-28 13:00:03.674
So far everything looks okay. A better file format would make this much much easier.
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 (2)

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.


talha iqbal
talha iqbal on 26 May 2023
Edited: talha iqbal on 26 May 2023
What about Noon (12PM) or Midnight (12AM) ?

Categories

Find more on Data Type Conversion 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!