convert 12 hour data to 24 hour datetime data for timetable

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.

Answer 1

[Stephen23]

Edit: read the comments to handle ambiguous midday/midnight:

https://en.wikipedia.org/wiki/12-hour_clock#Confusion_at_noon_and_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'}

[Poison Idea fan]

This incorrectly adds 12 hours to cases at 12AM and 12 PM.

[Stephen23]

"This incorrectly adds 12 hours to cases at 12AM and 12 PM."

Aaah, everyone's favorite ambiguous times of day:

https://en.wikipedia.org/wiki/12-hour_clock#Confusion_at_noon_and_midnight

I am guessing that you intend 12AM=midnight and 12PM=midday. Here are two approaches to handle that.

MeasurementTime = datetime({'2015-12-18 12:00:00';'2015-12-18 12:00:01';'2015-12-18 01:00:00';'2015-12-18 11:00:59';'2015-12-18 12:00:00';'2015-12-18 12:00:59';'2015-12-18 11:00:59'});
Temp = [23;37.3;38;39.1;42.3;64;99];
Pressure = [28;30.1;30;30.03;29.9;28;32];
WindSpeed = [17;13.4;11;6.5;7.3;5;7];
AMPM = cellstr(['AM';'AM';'AM';'AM';'PM';'PM';'PM']);
TT = timetable(MeasurementTime,Temp,Pressure,WindSpeed,AMPM)

TT = 7×4 timetable
      MeasurementTime       Temp    Pressure    WindSpeed     AMPM 
    ____________________    ____    ________    _________    ______

    18-Dec-2015 12:00:00      23        28          17       {'AM'}
    18-Dec-2015 12:00:01    37.3      30.1        13.4       {'AM'}
    18-Dec-2015 01:00:00      38        30          11       {'AM'}
    18-Dec-2015 11:00:59    39.1     30.03         6.5       {'AM'}
    18-Dec-2015 12:00:00    42.3      29.9         7.3       {'PM'}
    18-Dec-2015 12:00:59      64        28           5       {'PM'}
    18-Dec-2015 11:00:59      99        32           7       {'PM'}

1- Use DATETIME itself, e.g.:

dt = datetime(strcat(string(TT.MeasurementTime),TT.AMPM),'InputFormat','d-MMM-y h:m:sa')

dt = 7×1 datetime array
   18-Dec-2015 00:00:00
   18-Dec-2015 00:00:01
   18-Dec-2015 01:00:00
   18-Dec-2015 11:00:59
   18-Dec-2015 12:00:00
   18-Dec-2015 12:00:59
   18-Dec-2015 23:00:59

Although it looks temptingly easy, note that this approach may/will lose precision during the string conversion.

2- Or we can modify my earlier code to handle those cases, at full precision:

hh = TT.MeasurementTime.Hour;     % modified
ip = strcmpi(TT.AMPM,'PM');       % modified
ix = (ip & hh<12)-(~ip & hh==12); % modified
TT.MeasurementTime = TT.MeasurementTime + hours(12).*ix

TT = 7×4 timetable
      MeasurementTime       Temp    Pressure    WindSpeed     AMPM 
    ____________________    ____    ________    _________    ______

    18-Dec-2015 00:00:00      23        28          17       {'AM'}
    18-Dec-2015 00:00:01    37.3      30.1        13.4       {'AM'}
    18-Dec-2015 01:00:00      38        30          11       {'AM'}
    18-Dec-2015 11:00:59    39.1     30.03         6.5       {'AM'}
    18-Dec-2015 12:00:00    42.3      29.9         7.3       {'PM'}
    18-Dec-2015 12:00:59      64        28           5       {'PM'}
    18-Dec-2015 23:00:59      99        32           7       {'PM'}

Please test this and let me know if it works on your data sets. If it works, I will add it to my answer.

[Poison Idea fan]

Maybe I'm missing something, but datetime isn't working I think because of how I'm loading the data. I've pasted my code for how I call the txt file.

Trying the other method, my Time variable doesn't have a property for Hour even if I convert the raw table to a timetable.

Maybe there is a better way to import the data file that can avoid this issue in the first place.

opts = detectImportOptions('CRD445_20220427.txt');

opts = setvaropts(opts,'Var3','InputFormat','MM/dd/uuuu');

raw_445CRD_table = readtable('CRD445_20220427.txt',opts);

raw_445CRD_table.Properties.VariableNames = {'Time','AMPM','Date','445nm tau'}; 

CRD_TT = table2timetable(raw_445CRD_table); % Convert to timetable

[Stephen23]

"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]

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.

Answer 2

[Peter O]

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   

[Poison Idea fan]

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