How can i split time series by hour

Hello,

How can i split attached time series in 2 different intervals and get a new xls file: for example: 80300000 to 90300200 and 93155900 to 94640000

[dpb]

How have you read the data and what's the interpretation of the time field?

[William Chamberlain]

For anyone else coming here from Google;

- copying data from a specific period in one timeseries into a new timeseries can be done with the getsampleusingtime function.

- you should be able to get the start and end times from the timeseries.TimeInfo.Start/End fields and work out the breakpoints (hour=x, min=0, sec=0) to pass to getsampleusingtime from there.

Answer 1

[Peter Perkins]

If you are using R2016b or later, this is straight-forward with timetables. If your goal is really just to read an Excel file and write out two more, the following may seem like overkill, but I assume you might want to do more that that in MATLAB.

>> t = readtable('Matlab_TEST.xls')
t =
  62400×4 table
      DATA        HOUR          x            y    
    ________    ________    _________    _________
    20170714    80300000    57.706476    49.119791
    20170714    80300100    57.706476    49.119791
    20170714    80300200    57.706476     49.10869
    20170714    80300300    57.706476    49.097589
    20170714    80300400    57.715175    49.097589
    20170714    80300500    57.706476    49.097589
    20170714    80300600    57.715175    49.086488
    20170714    80300700    57.715175    49.086488
[snip]
>> t.TIME = datetime(t.DATA,'ConvertFrom','yyyymmdd','Format','dd-MMM-yyyy HH:mm:ss.SSS');
>> h = floor(t.HOUR/10000000);
>> m = floor((t.HOUR-10000000*h)/100000);
>> s = floor((t.HOUR-10000000*h-100000*m)/1000);
>> ms = floor((t.HOUR-10000000*h-100000*m-1000*s));
>> t.TIME = t.TIME + duration(h,m,s,ms);
>> tt = table2timetable(t(:,{'TIME','x','y'}))
tt =
  62400×2 timetable
              TIME                  x            y    
    ________________________    _________    _________
    14-Jul-2017 08:03:00.000    57.706476    49.119791
    14-Jul-2017 08:03:00.100    57.706476    49.119791
    14-Jul-2017 08:03:00.200    57.706476     49.10869
    14-Jul-2017 08:03:00.300    57.706476    49.097589
    14-Jul-2017 08:03:00.400    57.715175    49.097589
    14-Jul-2017 08:03:00.500    57.706476    49.097589
    14-Jul-2017 08:03:00.600    57.715175    49.086488
    14-Jul-2017 08:03:00.700    57.715175    49.086488
[snip]
>> tr1 = timerange('14-Jul-2017 08:03:00','14-Jul-2017 09:03:02','closed')
tr1 = 
  timetable timerange subscript:
    Select timetable rows with times in the closed interval:
    [14-Jul-2017 08:03:00, 14-Jul-2017 09:03:02]
  See Select Timetable Data by Row Time and Variable Type.
>> tt1 = tt(tr1,:)
tt1 =
  33021×2 timetable
              TIME                  x            y    
    ________________________    _________    _________
    14-Jul-2017 08:03:00.000    57.706476    49.119791
    14-Jul-2017 08:03:00.100    57.706476    49.119791
    14-Jul-2017 08:03:00.200    57.706476     49.10869
    14-Jul-2017 08:03:00.300    57.706476    49.097589
    14-Jul-2017 08:03:00.400    57.715175    49.097589
    14-Jul-2017 08:03:00.500    57.706476    49.097589
    14-Jul-2017 08:03:00.600    57.715175    49.086488
    14-Jul-2017 08:03:00.700    57.715175    49.086488
[snip]
    14-Jul-2017 09:03:01.300    50.860219    79.225745
    14-Jul-2017 09:03:01.400    50.886315    79.236847
    14-Jul-2017 09:03:01.500    50.877616    79.259049
    14-Jul-2017 09:03:01.600    50.851519    79.270149
    14-Jul-2017 09:03:01.700    50.851519    79.270149
    14-Jul-2017 09:03:01.800    50.808025    79.270149
    14-Jul-2017 09:03:01.900     50.77323    79.247947
    14-Jul-2017 09:03:02.000    50.729735    79.259048
>> tr2 = timerange('14-Jul-2017 09:31:55.900','14-Jul-2017 09:46:40','closed');
>> tt2 = tt(tr2,:);

That all uses datetimes, and if your data are all intraday, you might want to use durations. The "time-of-day" version looks like this:

>> tt.TIME = timeofday(t.TIME);
>> tt.TIME.Format = 'hh:mm:ss.SSS'
tt =
  62400×2 timetable
        TIME            x            y    
    ____________    _________    _________
    08:03:00.000    57.706476    49.119791
    08:03:00.100    57.706476    49.119791
    08:03:00.200    57.706476     49.10869
    08:03:00.300    57.706476    49.097589
    08:03:00.400    57.715175    49.097589
    08:03:00.500    57.706476    49.097589
    08:03:00.600    57.715175    49.086488
    08:03:00.700    57.715175    49.086488
[snip]
>> tr1 = timerange(duration(08,03,00),duration(09,03,02),'closed')
tr1 = 
  timetable timerange subscript:
    Select timetable rows with times in the closed interval:
    [08:03:00, 09:03:02]
  See Select Timetable Data by Row Time and Variable Type.
>> tt1 = tt(tr1,:)
tt1 =
  33021×2 timetable
        TIME            x            y    
    ____________    _________    _________
    08:03:00.000    57.706476    49.119791
    08:03:00.100    57.706476    49.119791
    08:03:00.200    57.706476     49.10869
    08:03:00.300    57.706476    49.097589
    08:03:00.400    57.715175    49.097589
    08:03:00.500    57.706476    49.097589
    08:03:00.600    57.715175    49.086488
    08:03:00.700    57.715175    49.086488
[snip]
    09:03:01.300    50.860219    79.225745
    09:03:01.400    50.886315    79.236847
    09:03:01.500    50.877616    79.259049
    09:03:01.600    50.851519    79.270149
    09:03:01.700    50.851519    79.270149
    09:03:01.800    50.808025    79.270149
    09:03:01.900     50.77323    79.247947
    09:03:02.000    50.729735    79.259048
>> tr2 = timerange(duration(09,31,55,900),duration(09,46,40),'closed');
>> tt2 = tt(tr2,:);