How can I convert one date and time from two colums?

Im trying to convert the first two columns of a cell into a Matlab time. first column {1,1} is the date in YYYY-MM-DD format and the second it the time in HH:MM format. Any ideas where I'm going wrong?

file = 'D:\Beach Erosion and Recovery\Bournemouth\Bournemouth Tidal Data\tidal_data_jtide.txt'
fileID = fopen(file);
LT_celldata = textscan(fileID,'%D%D%D%D%d%[^\n\r]','delimiter',',');
formattime = 'yyyy-mm-dd HH:MM'
date = LT_celldata{1,1};
time = LT_celldata{1,2};
date_time = datenum('date','time');

[Image Analyst]

You forgot to attach tidal_data_jtide.txt.

Come on, make it easy for us to help you.

[dpb]

Almost certainly '%D%D%D%D%d%[^\n\r]' isn't the right format string unless there are four columns of date info...show us the actual input format for the file first and let's try to solve the problem on import instead of patching up afterwards.

Specifically in the code you've referenced only one specific cell in the indexing expressions {1,1} and {1,2} not the columns and you've passed the literal strings 'date' and 'time' to the datenum function instead of the variables date and time.

datenum has been deprecated; use datetime instead unless your're stuck with a (by now getting quite) old release.

Lastly, when we get the file format; it probably will make a lot of sense to use readtable to create a table instead of textscan and get the benefits thereof in processing ease, or since you apparently have one, possibly converting that to a timetable or timeseries object may be "even more better"...

[dpb]

There was a subsequent Q? on how to find the time associated with the minimum by day; I posted a partial answer/comment but now I can't see that...did it go away?

BTW, as part of that, it seemed in the end keeping the date and the times as separate variables turned out to be more helpful.

Answer 1

[dpb]

Another perturbation of Star's approach...

filename = 'tidal_data_jtide.txt';
TD = readtable(filename);                                 % Read File (used shorter var name--dpb :) )
DT1=datetime(join([string(TD.Var1) string(TD.Var2)]));  % first date, time a string; convert to ‘datetime’
DT2=datetime(join([string(TD.Var3) string(TD.Var4)]));  % ditto second
units= {'',t.Var7{1},''};                               % build cellstr for units
TD=table(DT1,TD.Var5,categorical(TD.Var6), ...          % and then make the final table
         'VariableNames',{'Time','Height','Tide'});
TD.Properties.VariableUnits={'','TD.Var7,''};           % table() won't accept as named parameter pair

Saved just a short subsection of your input file...built the following from above

> TD
TD =
  12×3 table
            Time           Height    Tide
    ___________________    ______    ____
    2017-10-24 01:17:38    1.76      L   
    2017-10-24 02:57:31    1.92      H   
    2017-10-24 05:53:35       1      L   
    2017-10-24 10:45:01    2.06      H   
    2017-10-24 13:27:16    1.78      L   
    2017-10-24 15:07:16    1.92      H   
    2017-10-24 18:12:08    0.98      L   
    2017-10-24 23:00:16    1.95      H   
    2017-10-25 01:27:56    1.76      L   
    2017-10-25 03:28:01    1.97      H   
    2017-10-25 06:26:41    1.17      L   
    2017-10-25 10:47:04    1.96      H   
>>

From your initial post and observing that the two date/times are consistently one hour apart I didn't include the second in the table as being essentially redundant. If this changes, the fix is obvious.. :)

I also converted the tide indicator variable to categorical.

[Gwyn Hennessey]

Thank you :) The use of readtable makes the data processing so much easier

[dpb]

Yes...it has been a step forward...I've thought for years there should, however, be a way to convert time strings to datenum or datetime without a specific date; just the time so that one could write

DT1=datetime(TD.Var1)+datetime(TD.Var2);

for cases such as this, or that the '%{}D' format string should allow for stringing together a date and time formatting expression with delimiter as compound scan field. But, we're not there yet... :(

[Peter Perkins]

Beginning in R2018a, you can do this:

>> dstr = '3-Aug-2018'; tstr = '14:07:52';
>> dt = datetime(dstr) + duration(tstr)
dt = 
  datetime
   03-Aug-2018 14:07:52

Reading from a file should work similarly.

[dpb]

Yes,that is a good enhancement. Jeremy had pointed duration(string) in another comment to my complaint below...

Gwyn's subsequent mission as follow up to reading the data looking to find the time of a daily minimum turns out to raise some other shortcomings of how some of the grouping analysis functions work. I'm still mulling over how to best write an enhancement request for that issue but has to do with losing the specific time associated with a grouped set of datetime when doing an aggregation like @min. To get the actual minimum of the daily observations is very simple but it turns out a trick to figure out which one of multiple observations on the same day is the one associated with that minimum.

[Peter Perkins]

I think maybe what you're saying is that you'd like to group based on a transformation of one of the variables, rather than by the variable itself.

In the case of time, retime is one way to do that, using 'daily' etc. But I hear what (I think) you are saying.

[dpb]

The specific problem here is that retime and discretize for 'daily' inverval end up "throwing away" the time portion. For something like @mean that makes sense; the function really is an aggregation over the period. But, consider in Gwyn's case the function is @min over the height variable(*) -- the minimum height for the day is returned, correct, but there's no indication of which of those half-dozen or so daily observations is the one associated with that minimum....for that type of function it is significant that while the grouping is by day that there is a particular observation within that day that is the one associated with the function.

Hence the quandary I had on deciding just how to couch an enhancement request; thought needed more thinking over the possiblilities to get more organized before doing so.

Also in toying with solutions to the problem ran up again against the inability to return alternate/optional arguments for anonymous functions so couldn't get [minx,idx]=min(dailyvector) nor even [~,idx]=min(dailyvector) without writing an m-file and then one also runs into the issue here that the index returned even if it were possible syntax isn't the index in the overall table of interest but only the location in the daily subset.

(*) Gwyn's first attempt which seems reasonable was to have three variables of date, time and height and she then did retime over the table. The result was "correct" for the working definition of what retime is documented to do; it returned the minimum height and the "minimum time" for each day, the latter of which was, of course, the first observation of each day, not necessarily the corresponding time of day for the minimum tide height.

The ideal would be a way to tell retime that for the specific function being applied there is a specific time associated that is significant inside the group and that should be automagically the one returned, not just the group value as is presently done. BUT as noted, sometimes that isn't true, either, so it isn't a trivial issue to solve.

[Peter Perkins]

Yes, that's a tough one. I confess I did not read this entire thread carefully enough to grasp all the details.

There's certainly a work-around for the "need to write an M-file" issue, but yes, anonymous functions inline are much more convenient.

The "need to know the original index" is tougher to solve within the constraints of retime. I'm inclined to think that using rowfun might be simpler, but you would need to add two additional variables before calling rowfun: the date (or whatever you are grouping on if it isn't in the timetable already), and an index variable. I agree that this seems like something that should be simpler.

Answer 2

[Star Strider]

One approach:

filename = 'tidal_data_jtide.txt';
TideData = readtable(filename);                                             % Read File
Days1 = datevec(TideData.Var1);                                             % Year-Month-Day
Hours1 = datevec(TideData.Var2);                                            % Hour:Minute:Second
DT1 = datetime([Days1(:,1:3), Hours1(:,4:6)]);                              % Concatenate Date Vectors & Convert To ‘datetime’
Days2 = datevec(TideData.Var3);                                             % Year-Month-Day
Hours2 = datevec(TideData.Var4);                                            % Hour:Minute:Second
DT2 = datetime([Days2(:,1:3), Hours2(:,4:6)]);                              % Concatenate Date Vectors & Convert To ‘datetime’

TideData = table(DT1, DT2, TideData.Var5, TideData.Var6, TideData.Var7);    % Result - Create New Table

Check = TideData(1:10,:)                                                    % Optional - Delete Later

[Jeremy Hughes]

This could be simplified:

TideData = readtable(filename);       
% time of day may or may not be needed depending on 
% the types you're getting out of the file                                                                           
DT1 = TideData.Var1 + timeofday(TideData.Var2); 
DT2 = TideData.Var3 + timeofday(TideData.Var4);

If you're using R2018a, there are convenience functions for transforming tables. The benefit here is that it easily extends to many variables in the table without needed to explicitly name them.

TideData = removevars(TideData,1:4);
TideData = addvars(TideData,DT1,DT2,'Before',1);

Hope this helps,

Jeremy

[dpb]

I'd either forgotten or was never aware of timeofday, Jeremy...that aids somewhat in my complaint in other comment.

[Jeremy Hughes]

In R2018a, readtable should be able to get duration for text like '12:30:21' without specifying anything. If it's 'hh:mm' or 'mm:ss' you'll have to specify the right format.

Answer 3

[Gwyn Hennessey]

file atteched