MATLAB Answers

0

How to convert column datetime to datenum?

Asked by Louise Wilson on 9 May 2019
Latest activity Commented on by Louise Wilson on 9 May 2019
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

  2 Comments

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.

Sign in to comment.

Products


Release

R2018a

1 Answer

Answer by Stephen Cobeldick on 9 May 2019
Edited by Stephen Cobeldick on 9 May 2019
 Accepted Answer

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:

  7 Comments

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!
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.)
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

Sign in to comment.