From date and time in different columns to datetime

Hey, I have an array with date (yyyy-mm-dd) in one columns and time (hh:mm:ss) in another like this:

2017-09-29 21:00:00

2017-09-29 22:00:00

2017-09-29 23:00:00

2017-09-30 00:00:00

2017-09-30 01:00:00

2017-09-30 02:00:00

2017-09-30 03:00:00

How can I make this array into a datetime array?

[Andrei Bobrov]

Please attach an example of your data (as mat-file) so that we know what type of data you are using.

Answer 1

[Akira Agata]

Assuming your data is stored in CSV file like the attached, readtable function automatically recognize that 1st and 2nd column are datetime and duration. So, the following can make the datetime vector you want.

T = readtable('data1.csv');
time = T{:,1}+T{:,2};

[Espen Mikkelsen]

I have tried, but I recieved following error message: "Addition is not defined between datetime arrays".

[Akira Agata]

What is your MATLAB version? At least the latest one (R2018a), this code works. I would recommend checking the type of 1st and 2nd column of T. They should be datetime and duration, respectively.

[Peter Perkins]

This will only work for R2018a. Prior to that, do this:

T.date = T.date + timeofday(T.time)

This assumes your CSV has column headers "date" and "time".

[Louise Wilson]

I am now trying this and I get the error:

"Addition is not defined between cell and duration arrays."

I'm using R 2018B and loaded my data using readtable.

Can anyone help? What does a cell array mean? And how do I change it? Thank you

dd = 'input_data';
nowd = cd;                  
d = dir('*.csv');


for j=1:length(d)
tic
    
    filename=d(j).name;
    disp(filename);
    
    dat=readtable(filename);
    %data=table2timetable(dat, 'RowTimes', 'LOCALTIME');                    %orientate timetable using 'localtime' as the time vector
    
    try 
    fid = fopen(fullfile(dd,filename)); 
    
    data=removevars(dat, [1 2 3 4 7 9 10]);                              %remove columns I am not interested in
    
    time=data{:,1}+data{:,2};
  
   
    
    catch
             disp('error');
             
    fclose(filename);
    
    end

end

[Peter Perkins]

Thsi seems to be in the wrong thread.

"cell array" means that for some reason, readtable believes that the colummn in your CSV that you want to treat as dates, is text. Not sure why that is, but the solution is to use detectimportoptions and then adjust things to tell it that that column is in fact dates.

[Felix123]

@Peter Perking: can you be more specific on how to adjust these. I have not ever had a spreadsheet, where this was figured out automatically.

[Peter Perkins]

It's described in the examples here:

https://www.mathworks.com/help/matlab/ref/detectimportoptions.html

If a recent version of readtable (NOT xlsread) can't automatically identify date columns, then there's something in the spreadsheet that's not "right". The cells in the spreadsheet may not be formatted as dates. There may be a mix of text and other things. Hard to say for sure, but recent versions of readtable are pretty good at detecting dates.

Answer 2

[Andrei Bobrov]

datetime('2017-09-29') + hours(21:27)'