# convert 12 hour data to 24 hour datetime data for timetable

31 visualizzazioni (ultimi 30 giorni)
Poison Idea fan il 15 Mar 2022
Commentato: Poison Idea fan il 31 Mag 2023
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.
##### 0 CommentiMostra -2 commenti meno recentiNascondi -2 commenti meno recenti

Accedi per commentare.

### Risposta accettata

Stephen23 il 16 Mar 2022
Modificato: Stephen23 il 29 Apr 2022
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 CommentiMostra 3 commenti meno recentiNascondi 3 commenti meno recenti
Stephen23 il 29 Apr 2022
Modificato: Stephen23 il 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 = 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 il 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.

Accedi per commentare.

### Più risposte (2)

Peter O il 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 CommentoMostra -1 commenti meno recentiNascondi -1 commenti meno recenti
Poison Idea fan il 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

Accedi per commentare.

talha iqbal il 26 Mag 2023
Modificato: talha iqbal il 26 Mag 2023
What about Noon (12PM) or Midnight (12AM) ?
##### 1 CommentoMostra -1 commenti meno recentiNascondi -1 commenti meno recenti
Poison Idea fan il 31 Mag 2023
Just exclude those cases.

Accedi per commentare.

### Categorie

Scopri di più su Data Type Conversion in Help Center e File Exchange

R2021b

### Community Treasure Hunt

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

Start Hunting!

Translated by