outerjoin between timetables changing the time format

Hi, I am attaching the variable.mat that contains g8067525, g8072050 table variables. I was trying to join them (after filling the time series gaps with NaNs) using outerjoin function. However, it changes the time format from "24-Aug-2017 00:00:00" to "24-Aug-2017" in the output file. Basically it is ignoring the hh:mm:ss part. I highly appreciate your help to make it showing the full time format. Thanks a lot.

t1 = datetime(2017,8,24,0,0,0); % start of simulation/usgs gage reading 
t2 = datetime(2017,9,1,0,0,0); % end of simulation/usgs age reading
t3 = t1:minutes(15):t2; % 15 minutes interval
t=t3'; % make it column vector

x={g8067525, g8072050}

 TT=timetable();
 for k=1:length(x) 
     x1=table2timetable(x{k});
     x2=retime(x1,t);
     TT=outerjoin(TT,x2);
 end
  T= timetable2table(TT);
  writetable(T,'usgs.dat','Delimiter','\t')

[Adam Danz]

I could not reproduce this problem in r2020b.

[Shuvashish Roy]

Thanks. I am using MATLAB R2017a. I don't know if it is beasue of the older version. Thanks a lot. Anyway, would you please tell me how to align the headers with the column values. Thanks again.

[Peter Perkins]

In recent versions of MATLAB, the "default" display of datetimes became more intelligent about whether or not a time portion needs to be displayed, which is why Adam doesn't see what you see.

I think this is because TT's (empty) row times had no time portion, so their display was set to "don't need to show a time portion". When combining datetime arrays with different formats, the first one "wins", so the result ended up using TT's format because it was first in your outerjoin call. I bet if you flipped the inputs to outerjoin you'd get your time back.

In any case, you can set the .Format in your result to 'default' and that should do it. But as I said, since R2019-something, this is no longer an issue. And just to be clear (and you already know this), it was just a display issue: the values were not losing their time portion, on the display.

[Shuvashish Roy]

Hi Peter,

Thanks a lot for your awsome solution! Flipping the (TT,x2) to (x2,TT) solved it. However, is there a way to define the time format in the empty matrix TT so that outerjoin(TT,x2) also shows the time portion? Thanks again for your time.

[Adam Danz]

You can set the format of the time column named "Time" in a timetable TT using

TT.Time.Format = 'dd-MMM-uuuu HH:mm:ss';  % Or whatever format you want to use

[Shuvashish Roy]

Awesome !!! Thanks a lot Adam and also Peter.

Answer 1

[Adam Danz]

The problem could not be reproduced in r2020b suggesting that his has been fixed since r2017a.

" would you please tell me how to align the headers with the column values."

If this file will be read back into matlab (or any other program that expects a constant delimiter) you should not align the columns.

If you want to do this for better human-readability, I suggest you write it to a spreadsheet instead of a text file.

Nevertheless, one way to control the position of the headers relative to the data columns is by adding spaces before or after the header names. But this can get messy and the result can differ between text editors. For example, to shift the header for T.t all the way to the left, you just need to add 19 spaces to the right of the label since "24-Aug-2017 00:00:00" has 20 spaces.

% char(32) results in a space character " ".
T.Properties.VariableNames{1} = [T.Properties.VariableNames{1},char(ones(1,19)*32)];

When this is printed to the Matlab console,

When I open that in notepad++,

When I open it in Matlab's editor,

When I open it in Microsoft's Notpad,

[Shuvashish Roy]

Thanks a lot, Adam.

[Adam Danz]

Glad I could help.

Of course you could convert the entire table to string values which would make it relatively easy to align the columns within a text file.

[Peter Perkins]

Nice trick, but "by adding spaces before or after the header names" is supported only recently, IIRC R2019b. Prior to that, spaces were stripped off.