How to convert column datetime to datenum?

HI everyone,

I have a .csv file with a datetime column, in the format 'yyyy/m/d hh:mm:ss', for example: '2019/5/2 10:21:25'.

So, earlier dates in the month will be yyyy/m/d but later this will change to yyyy/mm/dd.

I want to convert these datetimes to datenum, as code I am using from a colleague which relies on datenum as an input.

I tried the following, where data.DateTime is a column in a table of such datetimes.

formatIn = 'yyyy/m/d HH:MM:SS';
datenumber=datenum(data.DateTime,formatIn)

However, I get the error "too many input arguments".

Thank you in advance for your help!

Louise

[Walter Roberson]

Try

{data.DateTime}

[Louise Wilson]

Thank you Walter. I tried that but get some errors:

>> datenumber=datenum({data.DateTime},formatIn);
Error using datenum (line 190)
DATENUM failed.

Caused by:
    Error using datenum (line 118)
    The input to DATENUM was not an array of character vectors.

Answer 1

[Stephen23]

It is not clear from your example what class data has: a table or a non-scalar structure or .. ?

In any case, datetime objects do not need a format to be converted to serial date numbers:

DN = datenum(data.DateTime)   % If data is a table or a scalar structure
DN = datenum([data.DateTime]) % If data is a non-scalar structure

As the documentation shows, the format argument is only used for date string inputs:

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

[Louise Wilson]

Hi Stephen,

Thank you! Sorry, my data is in a table. This works but the values produced all look identical... maybe I am misunderstanding what this does but I expect each number to be different.

Data output attached:

[Louise Wilson]

Thanks Stephen, I got my original working from the page you linked, which does suggest format can be inputted. I thought, since my format is different to what I have seen in the examples, that I should make a nod to it?

[Stephen23]

"This works but the values produced all look identical... maybe I am misunderstanding what this does but I expect each number to be different."

To view more of the digits you need to change the format and/or the Variable Viewer formatting. Currently you are only looking at five digits (i.e. the default format short), which means the smallest shown difference corresponds to approximately one month and ten days:

>> datevec(0.0001e5)
ans =
    0    1   10    0    0    0

If those timestamps differ by less than this, then of course it is quite possible you will not see any difference when only viewing five digits of precision: if you want to view the values with a higher precsion, then you need to change the formatting!

Another option is to simply view the numbers as date vectors:

    datevec(DN)

and you will quickly see if they are different or not.

[Louise Wilson]

Thank you Stephen. I used datevec as you said and they are indeed different-I already knew they differed by seconds but this points out really clearly the differences and is a good tool to have. Thank you!!

[Louise Wilson]

Stephen-does it matter that the format of my date will change with time? I will have two mm rather than m later in the year? Thanks!

[Walter Roberson]

If you have a table object with a variable that is datetime objects (not cell array of datetime objects), then MATLAB will handle that by creating a (column) vector out of the datetime objects, and assigning that entire vector as a column in the table. When you create a vector out of datetime objects, the all take on the Format property of the first datetime object in the vector. It is therefore not possible to have a single datetime object column in a table that has two different formats.

Now, you might be reading in the dates as character vectors out of a file, probably by using readtable(). And for that purpose it can be a problem if the character vectors are not all the same format. If you do not get an outright error message, then the ones that are not in the first format might be assigned NaT (Not A Time). readtable() is usually pretty good about examining a few input lines to try to figure out what the format is, but it can have problems. You might need to pass a Format parameter to readtable() or use detectImportOptions (possibly along with zapping the Format that it generates for that column.)

[Louise Wilson]

Hi Walter,

Thank you. I am new to Matlab so don't fully understand your answer, but I have everything working now...

I had used readtable like you said and made adjustments to the formatting of each cell.

Here is my full code, what do you think?

dd = 'input_data';
nowd = cd;                  %current folder
cd(dd);                     %go to input folder
d = dir('*.csv');
%cd(nowd);                  %GO BACK TO current folder


for j=1:length(d)

    filename=d(j).name;
    disp(filename);
    
    data=readtable(filename);
    %data=table2timetable(dat, 'RowTimes', 'LOCALTIME');                   %orientate timetable using 'localtime' as the time vector
    
    try 
    fid = fopen(fullfile(dd,filename));                                    %open file
                                         
    
    %%Put Date and Time into One Column
    dates=datetime(data.LOCALDATE, 'Format', 'y/M/d');                     %convert date to datetime array
    times=datenum(data.LOCALTIME);                                         %datenum-serial date number
    
    t=table(dates,times);                                                  %create 2x table of dates and times
    dates=datetime(t.dates,'Format', 'y/M/d hh:mm:ss');                    %format date cells
    times=datetime(t.times,'ConvertFrom','datenum','Format',...            %format time cells
                    'y/M/d hh:mm:ss');
    fullt=dates+timeofday(times);                                          %date and time into one column
    data.DateTime=fullt;                                                   %append column onto data table
   
    
    %%Convert DateTime to DateNum
    DateNumber=datenum(data.DateTime);
    data.DateNumber=DateNumber;
    %datevec(datenumber);                                                  %check that the datenumbers are different
    
    
    %%Remove date/time columns I am not interested in
    data_new=removevars(data, [1 2 3 4 5 6 8 10 13]);  
    
    
    %%Add callsign or vessel number of boat:
    data_new.MNZ=('111111');
    
    
    catch
             disp('error');
             
    fclose(filename);
    
    end

end