How do I convert tall array duration time vector to HH:mm:ss for merging with tall array datetime vector ?

Need some help with this. I have a tall array date vector 'yyyy-MM-dd' of datetime format, and a tall array time vector 'HH:mm:ss.SSS' of duration format. How do I convert the duration vector to only HH:mm:ss (omitting the decimals) and then combine that with the date vector to get: 'yyyy-MM-dd HH:mm:ss' ?

Answer 1

[Chris]

dates = tall(repmat(datetime(date),3,1))

dates =

  3×1 tall datetime array

   21-Jan-2022
   21-Jan-2022
   21-Jan-2022

times = tall(repmat(duration(15,12,1,345,'Format','hh:mm:ss.SSS'),3,1))

times =

  3×1 tall duration array

   15:12:01.345
   15:12:01.345
   15:12:01.345

times.Format = 'hh:mm:ss';
datetime([string(dates) + " " + string(times)])

ans =

  3×1 tall datetime array

   21-Jan-2022 15:12:01
   21-Jan-2022 15:12:01
   21-Jan-2022 15:12:01

[Douglas Leaffer]

Chris, thank you. Running yur code, the following errors output. Your suggestion for a fix would be welcome.

Error using + (line 48)

Incompatible tall array arguments. The first dimension in each tall array must

have the same size, or have a size of 1.

Error in phase1_datetime (line 20)

datetime([string(dates) + " " + string(times)]);

[Chris]

Hi Douglas,

The error appears to indicate your date and time vectors do not have the same number of rows.

They should both have Size Nx1 for my code to work.

dates = tall(repmat(datetime(date),3,1));
times = tall(repmat(duration(15,12,1,345,'Format','hh:mm:ss.SSS'),3,1));
whos dates times

  Name       Size            Bytes  Class    Attributes

  dates      3x1                49  tall               
  times      3x1                73  tall               

The sizes here are 3x1.

You will also run into problems if any of the times is outside of 0-24 hours or if you have negative dates. In that case, Walter's suggestion for adding the times and dates might need to be considered.

[Douglas Leaffer]

The date and time vectors are the same length. I think perhaps the problem lies with the way the .txt file is imported. I have attached an excerpt of the file if you have bandwidth to suggest further help. The columns of interest are date = column 7; time = column 8

[Chris]

This works for this excerpt:

data = readtable('excerpt.txt');
dates = data.Var7;
times = data.Var8;
result = datetime([string(dates) + " " + string(times)]);

I'm not sure how it works if the file is large enough to require tall arrays, though.

[Douglas Leaffer]

Thanks Chris ! Yes that worked for the excerpt.txt file. I sincerely appreciate your help/ Next is to do the same for the tall array (12 million lines), which is also a .txt file of same format. My suggested code for that is below:

ds = tabularTextDatastore('fulldata.txt','TreatAsMissing','NA','MissingValue',0);
ds.SelectedVariableNames = {'Var7','Var8'}; % Date Time; 
t = tall(ds);
dates = gather(t.Var7); % date
times = gather(t.Var8); % time 
% I assume I can use your code here:
result = datetime([string(dates) + " " + string(times)]);

[Douglas Leaffer]

that code also works for the tall array

[Douglas Leaffer]

Hello Chris,

Your code worked well: DateTime = datetime([string(dates) + " " + string(times)]); Time = string(times); %

A new data table I am trying to merge has 3 columns of {cell}, duration, and double, as below. Here I need to convert the {cell} to date (format: DD-MMM-YYYY) and then combine that date with time for datetime. Any suggestions?

{'2016/7/1'} 02:55:00 12000

{'2016/7/1'} 02:55:30 17500

{'2016/7/1'} 02:56:00 16900

{'2016/7/1'} 02:56:30 18400

[Chris]

Hello again, Douglas.

How about the following?

dates = datestr(theTable{:,1},'dd-mmm-yyyy');
durs = datestr(theTable{:,2},'HH:MM:SS');
merged = datetime(dates + " " + durs);

You could also shift the table around:

theTable.Var1 = merged;
theTable.Var2 = [];

[Douglas Leaffer]

Chris, that works fine. Thank you for your suggestions.

Answer 2

[Walter Roberson]

perhaps

[h,m,s] = hms(DURATION_COLUMN);
DATETIME_COLUMN = TIME_COLUMN + duration(h,m,floor(s))

or perhaps

DATETIME_COLUMN = dateshift(TIME_COLUMN + DURATION_COLUMN, 'start', 'second')

if you do not have negative durations or negative datetimes, then adding first and then getting rid of the fractions of a second should give the same result.