用日期时间值/计算填充表格的更好方法

Question

这是我的输入：

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     

如果PUMP = TRUE意味着泵打开了。我得看看每小时打开多少分钟。因此，我寻找下一个PUMP = FALSE，并计算直到它关闭的分钟数，依此类推。这最终是我存储在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 

我的问题是，计算这一点最有效的方法是什么？我现在用这些步骤来做，但需要很长时间：

步骤1:为每分钟的日期范围创建一个分钟数据集：

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

步骤2：循环通过我的输入并在我的分钟数据集中设置状态

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

步骤3：循环浏览我的分钟数据集，并用正确的值设置之间的单元格

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 

步骤4：为给定的日期范围创建一个小时数据集，并将它们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

也许有什么我不知道的日期函数可以保存一些循环？谢谢

当前数据库查询

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)
      )";

Answer 1

假设Converted Time是一个实际的DateTime，为什么不简单地使用它来确定适当的开始和结束时间，然后简单地计算差值呢？

添加一个不能正常工作的示例，但旨在演示其中的一些概念：

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

将结果存储在：

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

快速&脏且无法正常工作：

        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));
        }

编辑：我刚刚更新了主要功能；它仍然不是100%，但它接近于一个解决方案，但这是你需要解决的问题。

Answer 2

对于每个启动/停止循环，您可以使用以下内容：

        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);
        }