Calculation of events duration time at for a specific period

Hi Everyone,

I've this code that able to create a xlsx file that holds the electricity tariffs over a year, depends on the hour and the season, it called Time of use,

I am interested to calculate the duration time (in hours) over one year, for every tariff, and save this data in the same table (or another table it's fine too).

for example : Tariff 0.2307 was active for 2000 Hours...etc.

Thanks in advance !

%% Define date&time arrays :

t1 = datetime(2021,1,1,0,0,0);
t2 = datetime(2021,12,31,23,0,0);
dt = (t1:hours(1):t2)'

%holidyas date array

Holidays = [datetime(2021,3,27,0,0,0) datetime(2021,3,28,0,0,0) datetime(2021,4,2,0,0,0) datetime(2021,4,3,0,0,0) datetime(2021,4,14,0,0,0) datetime(2021,4,15,0,0,0) datetime(2021,5,16,0,0,0) datetime(2021,5,17,0,0,0) datetime(2021,9,6,0,0,0) datetime(2021,9,7,0,0,0) datetime(2021,9,15,0,0,0) datetime(2021,9,16,0,0,0) datetime(2021,9,20,0,0,0) datetime(2021,9,27,0,0,0)]
Holidays = Holidays'

DateNtime=zeros(numel(dt),2); %preallocating

%% All Seasons Logic :

%% Hours between 00:00 - 16:00 "Off Peak" - Days of week. (All Seasons)

for i=1:numel(dt)

    if hour(dt(i))>=0 & hour(dt(i))<=16 & ismember(day(dt(i),'dayofweek'),[1 2 3 4 5])  
        switch month(dt(i))
            case {12,1,2}  %% Season #1
                DateNtime(i,2)=0.2307;
            case {3,4,5,10,11}  %% Season #2
                DateNtime(i,2)=0.2243;
            case {6,7,8,9}  %% Season #3
                DateNtime(i,2)=0.2701;
end
end
end

%% On Peak - Days of week only (All Seasons)

for i=1:numel(dt)

    if hour(dt(i))>=17 & hour(dt(i))<=21 & ismember(day(dt(i),'dayofweek'),[1 2 3 4 5])  
        switch month(dt(i))
            case {12,1,2} %% Season #1
                DateNtime(i,2)=0.7170;
            case {3,4,5,10,11} %% Season #2
                DateNtime(i,2)=0.2578;
            case {6,7,8,9} %% Season #3
                DateNtime(i,2)=1.0789;
end
end
end

%% Season #1 Logic :


%% Hours between 00:00 - 16:00 "Off Peak" - Days of week. (Season#1)

for i=1:numel(dt)

    if hour(dt(i))>=0 & hour(dt(i))<=16 & ismember(day(dt(i),'dayofweek'),[6 7 ])  
        switch month(dt(i))
            case {12,1,2}  %% Season #1
                DateNtime(i,2)=0.2307;

end
end
end

%% Hours between 22:00 - 23:00 "Off Peak" - All Week . (Season#1 only)

for i=1:numel(dt)

    if hour(dt(i))>=22 & hour(dt(i))<=23 & ismember(day(dt(i),'dayofweek'),[1 2 3 4 5 6 7])  
        switch month(dt(i))
            case {12,1,2}  %% Season #1
                DateNtime(i,2)=0.2307;
end
end
end

%% Hour 23:00 "Off Peak" - Days of week. (Season#1)

for i=1:numel(dt)

    if hour(dt(i))== 23 & ismember(day(dt(i),'dayofweek'),[1 2 3 4 5])  
        switch month(dt(i))
            case {12,1,2}  %% Season #1
                DateNtime(i,2)=0.2307;

end
end
end

%% Weekends On Peak (Season #1)

for i=1:numel(dt)

if hour(dt(i))>=17 & hour(dt(i))<=21 & ismember(day(dt(i),'dayofweek'),[6 7])  

        switch month(dt(i))

            case {12,1,2}  %% Season #1
                DateNtime(i,2)=0.7170;

end
end
end


%% Holidays On Peak  (Seasons #1)

doy = day (Holidays(:,:),'dayofyear')
for i=1:numel(dt)

    if hour(dt(i))>=17 & hour(dt(i))<=21 & ismember(day(dt(i),'dayofyear'),doy)  
        switch month(dt(i))
            case {12,1,2}  %% Season #1
                DateNtime(i,2)=0.7170;

end
end
end

%% Holidays Off Peak 00:00 - 16:00  (Seasons #1)

doy = day (Holidays(:,:),'dayofyear')
for i=1:numel(dt)

    if hour(dt(i))>=00 & hour(dt(i))<=16 & ismember(day(dt(i),'dayofyear'),doy)  
        switch month(dt(i))
            case {12,1,2}  %% Season #1
                DateNtime(i,2)=0.7170;

end
end
end

%% Holidays Off Peak 22:00 - 23:00  (Seasons #1)

doy = day (Holidays(:,:),'dayofyear')
for i=1:numel(dt)

    if hour(dt(i))>=22 & hour(dt(i))<=23 & ismember(day(dt(i),'dayofyear'),doy)  
        switch month(dt(i))
            case {12,1,2}  %% Season #1
                DateNtime(i,2)=0.7170;

end
end
end



%% Hour 23:00 "Off Peak" - Days of week. (Season#2 & Season #3)

for i=1:numel(dt)

    if hour(dt(i))== 23 & ismember(day(dt(i),'dayofweek'),[1 2 3 4 5])  
        switch month(dt(i))
            case {3,4,5,10,11} %% Season #2
                DateNtime(i,2)=0.2243;
            case {6,7,8,9} %% Season #3
                DateNtime(i,2)=0.2701;
end
end
end



%% Weekends. (Season #2 & Season #3)

for i=1:numel(dt)

    if ismember(day(dt(i),'dayofweek'),[6 7])  
        switch month(dt(i))

            case {3,4,5,10,11} %% Season #2 
                DateNtime(i,2)=0.2243;
            case {6,7,8,9} %% Season #3
                DateNtime(i,2)=0.2701;
end
end
end




%% Hour 22:00 "On Peak" - Days of week. (Season#2 & Season #3)

for i=1:numel(dt)

    if hour(dt(i)) == 22 & ismember(day(dt(i),'dayofweek'),[1 2 3 4 5])  
        switch month(dt(i))
            case {3,4,5,10,11} %% Season #2
                DateNtime(i,2)=0.2578;
            case {6,7,8,9} %% Season #3
                DateNtime(i,2)=1.0789;
end
end
end

%% Holidays (Seasons #2 and #3)

doy = day (Holidays(:,:),'dayofyear')
for i=1:numel(dt)

    if ismember(day(dt(i),'dayofyear'),doy)  
        switch month(dt(i))

            case {3,4,5,10,11}  %% Season #2
                DateNtime(i,2)=0.2243;
            case {6,7,8,9}  %% Season #3
                DateNtime(i,2)=0.2701;
end
end
end



%% Creat Table to export :

A = dt(:,1);
B = DateNtime(:,2);
newTable = table(A,B);
writetable(newTable,'TIME_OF_USE.xlsx');

Answer 1

[Seth Furman]

It's not clear how you want to calculate these values.

Assuming you want to find the first and last timestamp for each tariff and take the difference, you could use groupsummary and then take the difference.

https://www.mathworks.com/help/matlab/ref/double.groupsummary.html

t = readtable("TIME_OF_USE.xlsx")

t = 8760×2 table
             A                B   
    ____________________    ______

    01-Jan-2021 00:00:00    0.2307
    01-Jan-2021 01:00:00    0.2307
    01-Jan-2021 02:00:00    0.2307
    01-Jan-2021 03:00:00    0.2307
    01-Jan-2021 04:00:00    0.2307
    01-Jan-2021 05:00:00    0.2307
    01-Jan-2021 06:00:00    0.2307
    01-Jan-2021 07:00:00    0.2307
    01-Jan-2021 08:00:00    0.2307
    01-Jan-2021 09:00:00    0.2307
    01-Jan-2021 10:00:00    0.2307
    01-Jan-2021 11:00:00    0.2307
    01-Jan-2021 12:00:00    0.2307
    01-Jan-2021 13:00:00    0.2307
    01-Jan-2021 14:00:00    0.2307
    01-Jan-2021 15:00:00    0.2307

tMin = groupsummary(t,"B","min")

tMin = 6×3 table
      B       GroupCount           min_A        
    ______    __________    ____________________

    0.2243       3048       01-Mar-2021 00:00:00
    0.2307       1710       01-Jan-2021 00:00:00
    0.2578        624       01-Mar-2021 17:00:00
    0.2701       2436       01-Jun-2021 00:00:00
     0.717        450       01-Jan-2021 17:00:00
    1.0789        492       01-Jun-2021 17:00:00

tMax = groupsummary(t,"B","max")

tMax = 6×3 table
      B       GroupCount           max_A        
    ______    __________    ____________________

    0.2243       3048       30-Nov-2021 23:00:00
    0.2307       1710       31-Dec-2021 23:00:00
    0.2578        624       30-Nov-2021 22:00:00
    0.2701       2436       30-Sep-2021 23:00:00
     0.717        450       31-Dec-2021 21:00:00
    1.0789        492       30-Sep-2021 22:00:00

tDuration = removevars(tMin,"min_A");
tDuration.Duration = tMax.max_A - tMin.min_A

tDuration = 6×3 table
      B       GroupCount     Duration 
    ______    __________    __________

    0.2243       3048       6599:00:00
    0.2307       1710       8759:00:00
    0.2578        624       6581:00:00
    0.2701       2436       2927:00:00
     0.717        450       8740:00:00
    1.0789        492       2909:00:00