Better way to fill table with Datetime values / calculations

Question

This is my input:

Converted Time          PUMP 
----------------------- -------- 
Do 30-Okt-2014 10:50:09 FALSE    
Do 30-Okt-2014 11:23:39 TRUE     
Do 30-Okt-2014 11:48:37 FALSE    
Do 30-Okt-2014 12:53:34 TRUE     
Do 30-Okt-2014 13:07:38 FALSE    
Do 30-Okt-2014 14:36:58 TRUE     
Do 30-Okt-2014 15:02:33 FALSE    
Do 30-Okt-2014 16:03:59 TRUE

If PUMP = TRUE means the pump is turned on. I have to look how many minutes it is turned on for each hour. So I look for the next PUMP = FALSE and calculate the minutes till it is turned off and so on. This is finally my output stored in a DataSet:

Hours                  Minutes_on
---------------------- ---------------- 
30.10.2014 11:00:00    25 
30.10.2014 12:00:00    7 
30.10.2014 13:00:00    7 
30.10.2014 14:00:00    24 
30.10.2014 15:00:00    2

And here is my question, what is the most efficient way to calculate this? I'm now doing it with these steps but it takes very long:

Step1: create a minute-dataset for the date range for every minute:

30.10.2014 11:23:00 empty 
30.10.2014 11:24:00 empty 
30.10.2014 11:25:00 empty 
30.10.2014 11:26:00 empty 
30.10.2014 11:27:00 empty 
30.10.2014 11:28:00 empty 
30.10.2014 11:29:00 empty 
30.10.2014 11:30:00 empty 
30.10.2014 11:31:00 empty 
30.10.2014 11:32:00 empty 
30.10.2014 11:33:00 empty 
30.10.2014 11:34:00 empty 
30.10.2014 11:35:00 empty 
30.10.2014 11:36:00 empty 
30.10.2014 11:37:00 empty 
30.10.2014 11:38:00 empty 
30.10.2014 11:39:00 empty 
30.10.2014 11:40:00 empty 
30.10.2014 11:41:00 empty 
30.10.2014 11:42:00 empty 
30.10.2014 11:43:00 empty 
30.10.2014 11:44:00 empty 
30.10.2014 11:45:00 empty 
30.10.2014 11:46:00 empty 
30.10.2014 11:47:00 empty 
30.10.2014 11:48:00 empty
30.10.2014 11:49:00 empty

Step2: Loop through my input and set the state in my minute-dataset

30.10.2014 11:23:00 On '-> is in my input table
30.10.2014 11:24:00 empty 
30.10.2014 11:25:00 empty 
30.10.2014 11:26:00 empty 
30.10.2014 11:27:00 empty 
30.10.2014 11:28:00 empty 
30.10.2014 11:29:00 empty 
30.10.2014 11:30:00 empty 
30.10.2014 11:31:00 empty 
30.10.2014 11:32:00 empty 
30.10.2014 11:33:00 empty 
30.10.2014 11:34:00 empty 
30.10.2014 11:35:00 empty 
30.10.2014 11:36:00 empty 
30.10.2014 11:37:00 empty 
30.10.2014 11:38:00 empty 
30.10.2014 11:39:00 empty 
30.10.2014 11:40:00 empty 
30.10.2014 11:41:00 empty 
30.10.2014 11:42:00 empty 
30.10.2014 11:43:00 empty 
30.10.2014 11:44:00 empty 
30.10.2014 11:45:00 empty 
30.10.2014 11:46:00 empty 
30.10.2014 11:47:00 empty 
30.10.2014 11:48:00 Off '-> is in my input table
30.10.2014 11:49:00 empty

Step 3: Loop through my minute-dataset and set the cells between with the right values

30.10.2014 11:23:00 On 
30.10.2014 11:24:00 On 
30.10.2014 11:25:00 On 
30.10.2014 11:26:00 On 
30.10.2014 11:27:00 On 
30.10.2014 11:28:00 On 
30.10.2014 11:29:00 On 
30.10.2014 11:30:00 On 
30.10.2014 11:31:00 On 
30.10.2014 11:32:00 On 
30.10.2014 11:33:00 On 
30.10.2014 11:34:00 On 
30.10.2014 11:35:00 On 
30.10.2014 11:36:00 On 
30.10.2014 11:37:00 On 
30.10.2014 11:38:00 On 
30.10.2014 11:39:00 On 
30.10.2014 11:40:00 On 
30.10.2014 11:41:00 On 
30.10.2014 11:42:00 On 
30.10.2014 11:43:00 On 
30.10.2014 11:44:00 On 
30.10.2014 11:45:00 On 
30.10.2014 11:46:00 On 
30.10.2014 11:47:00 On 
30.10.2014 11:48:00 Off
30.10.2014 11:49:00 Off

Step 4: create a hour-dataset for the given date range and sum the minutes where they On

Hours                  Minutes_on
---------------------- ---------------- 
30.10.2014 11:00:00    25 
30.10.2014 12:00:00    7 
30.10.2014 13:00:00    7 
30.10.2014 14:00:00    24 
30.10.2014 15:00:00    2

Maybe there is any date function I don't know to save some loops? Thanks!

Current database Query

command.CommandText = "WITH table_ AS 
                            (SELECT DATETIME, 
                             TOTALTIME1, 
                             RAWOUTPUT1, 
                             CASE BITAND(RAWOUTPUT1, POWER(2," +  (DD_einzelnePumpe.SelectedIndex - 1) + ")) 'e.g. POWER(2,0)
                                  WHEN 0 THEN 'FALSE' 
                                  ELSE 'TRUE' 
                             END AS Pumpe1_1, 
                             CASE BITAND(LAG(RAWOUTPUT1) OVER (ORDER BY datetime), POWER(2," + (DD_einzelnePumpe.SelectedIndex - 1) + ")) 
                                  WHEN 0 THEN 'FALSE' 
                                  ELSE 'TRUE' 
                             END AS priv_, 
                             CASE BITAND(LEAD(RAWOUTPUT1) OVER (ORDER BY datetime), POWER(2," + (DD_einzelnePumpe.SelectedIndex - 1) + ")) 
                                  WHEN 0 THEN 'FALSE' 
                                  ELSE 'TRUE' 
                             END AS next_ 
                     FROM pump_box_hist 
                     WHERE DATETIME > to_date('" + Start + "','dd/mm/yyyy hh24:mi:ss') 
                     AND DATETIME < to_date('" + Ende + "','dd/mm/yyyy hh24:mi:ss')
                      ) 
SELECT DATETIME, 
       TOTALTIME1, 
       RAWOUTPUT1, 
       Pumpe1_1 
FROM table_ 
WHERE ((Pumpe1_1 = next_ or next_ is null) 
       AND (Pumpe1_1 <> priv_ or priv_ is null)
      )";

Where does your data come from and what are the data formats, add some real code. — Peter, Mar 05 '15 at 09:21
I think you can best make a query that gets the dataset you want in step 4. So you need to post some sql. That would be the most efficient way. — Peter, Mar 05 '15 at 09:30

score 1 · Accepted Answer · edited May 23 '17 at 10:09

Assuming Converted Time is an actual DateTime, why not simply use that to determine the appropriate start and end time, and then simply calculate the difference?

Adding an example that isn't working properly, but is intended to demonstrate some of these concepts:

internal class LogEntry
{
    public LogEntry(DateTime dateTime, bool status)
    {
        DateTime = dateTime;
        Status = status;
    }

    public DateTime DateTime { get; set; }

    public bool Status { get; set; }
}

Storing results in:

internal class ResultEntry
{
    public ResultEntry(DateTime dateTime, int minutesOn)
    {
        DateTime = dateTime;
        this.MinutesOn = minutesOn;
    }

    public DateTime DateTime { get; set; }

    public int MinutesOn { get; set; }
}

Quick & dirty and not properly working:

        var logs = new List<LogEntry>
        {
            new LogEntry(new DateTime(2015, 3, 5, 11, 10, 15), true),
            new LogEntry(new DateTime(2015, 3, 5, 11, 20, 15), false),
            new LogEntry(new DateTime(2015, 3, 5, 12, 30, 15), true),
            new LogEntry(new DateTime(2015, 3, 5, 13, 40, 15), false),
            new LogEntry(new DateTime(2015, 3, 5, 14, 50, 15), true),
            new LogEntry(new DateTime(2015, 3, 5, 15, 10, 15), false)
        };

        var results = new List<ResultEntry>();

        for (var i = 1; i < logs.Count; i++)
        {
            var logEntry = logs[i];

            DateTime startDateTime;
            DateTime endDateTime;

            var roundedStartDateTime = new DateTime(logEntry.DateTime.Year, logEntry.DateTime.Month,
                logEntry.DateTime.Day, logEntry.DateTime.Hour, 0, 0);
            var roundedEndDateTime = roundedStartDateTime.AddHours(1);

            if (logEntry.Status)
            {
                startDateTime = logEntry.DateTime;

                endDateTime = roundedEndDateTime;

                if (i < logs.Count - 1)
                {
                    var nextLogEntry = logs[i + 1];

                    endDateTime = roundedEndDateTime < nextLogEntry.DateTime
                        ? roundedEndDateTime
                        : nextLogEntry.DateTime;
                }

            }
            else
            {
                var previousLogEntry = logs[i - 1];

                startDateTime = roundedStartDateTime > previousLogEntry.DateTime
                    ? roundedStartDateTime
                    : previousLogEntry.DateTime;

                endDateTime = logEntry.DateTime;
            }

            var minutesOn = Convert.ToInt32((endDateTime - startDateTime).TotalMinutes);
            results.Add(new ResultEntry(roundedStartDateTime, minutesOn));
        }

EDIT: I've just updated the main function; it's still not 100% but it is close to a solution, but that's something for you to solve.

yes its a `DateTime`, the problem is I need the minutes for each hours for a chart so I can't simply calculate between two dates — Kᴀτᴢ, Mar 05 '15 at 09:30
@katz Why not? The result of the difference is a `TimeSpan` which contains plenty of [useful properties](https://msdn.microsoft.com/en-us/library/system.timespan_properties(v=vs.110).aspx). — BCdotWEB, Mar 05 '15 at 09:31
maybe I understand something wrong but e.g. from 11:48 - 12:53 there are 1 hour and 5 minutes. so how do I know which of the 65 seconds belongs to hour 11 and which to hour 12? — Kᴀτᴢ, Mar 05 '15 at 09:42
@katz Simply determine the next "full" hour from the existing DateTime. I'll update my answer with an example; give me a minute. — BCdotWEB, Mar 05 '15 at 09:43

score 0 · Answer 2 · answered Mar 05 '15 at 10:19

For each start/ stop cycle you can use something like this:

        DateTime dtStart;
        DateTime dtStop;


        if (dtStop.Hour > dtStart.Hour) 
        {
            Console.WriteLine("minutes for {0} h = {1}", dtStart.Hour, 60 - dtStart.Minute);
            Console.WriteLine("minutes for {0} h = {1}", dtStop.Hour, dtStop.Minute);
        }
        else
        {
            Console.WriteLine("minutes for {0} h = {1}", dtStop.Hour, dtStop.Minute - dtStart.Minute);
        }

Better way to fill table with Datetime values / calculations

2 Answers2