数据表中不连续的小时数据,可以插入缺失的小时日期时间值和数据值为空
本文关键字:数据 小时 时间 日期 不连续 插入 数据表 | 更新日期: 2023-09-27 18:11:06
我正在使用以下存储过程从SQL Server检索数据:
SELECT *
FROM (
SELECT CAST(DateTimeUTC as SmallDateTime) as [DateTime], DataValue, VariableID
FROM DataValues
WHERE SiteID = @siteID and VariableID = 1
) TableDate
PIVOT (SUM(DataValue) FOR VariableID IN ([1])) PivotTable ORDER BY [DateTime]
然后用这些值填充DataSet DataTable:
DataSet myDataSet = new DataSet();
mySqlDataAdapter.Fill(myDataSet);
DataTable precip = myDataSet.Tables["Table"];
if (precip.Rows.Count > 0)
{
msPrec = precip.Rows[0].Field<DateTime>("DateTime");
sPrec = string.Join<object>(", ",
from r in precip.Rows.OfType<DataRow>() select r[1]);
}
else
{
msPrec = DateTime.MinValue;
sPrec = "";
}
在上面的例子中,msPrec只是数据集开头的单个值。
问题是我没有考虑到每小时的数据可能不是连续的。
这意味着可能存在未作为NULL存储在数据库中的丢失数据。因此,完全有可能一个DB记录来自2013-04-26 04:00:00,而DB中的下一个记录来自2013-04-26 08:00:00,缺少05:00,06:00和07:00。
也有可能多天的数据丢失,而不仅仅是同一天内的几个小时。
我用HighStock by HighCharts绘制了这个数据,就像这个例子中所示:http://www.highcharts.com/stock/demo/data-grouping除了他们在这个例子中预处理了数据,包括缺失数据的NULL值,正如你可以看到的图中的数据差距。
解决这个问题的伪代码对我来说很清楚:
-循环遍历存储过程
的输出-比较当前记录的日期时间与下一个记录的日期时间
-如果nextRecordDateTime = currentRecordDateTime + 1小时,继续下一个记录
-如果nextRecordDateTime = currentRecordDateTime + 1小时,则
以小时为单位计算currentRecordDateTime和nextRecordDateTime的差值
在DataTable中插入DateTime和DataValue (NULL)为每一个缺失的小时
-继续循环最后插入的记录
我查看了MSDN上的DataTable类,但没有看到任何相关的内容。DataRow类将允许我遍历行,但我不清楚如何在当前行中获得下一行的值。
我有两个问题;
我怎么能考虑下一个记录(行)的DateTime值,而在当前记录(行)的DataSet DataTable?
我如何在正确的位置插入数据集数据表中的行,而不是将其添加到数据表的末尾?或者这里的解决方案是将它添加到表的末尾并对表进行排序?
我编写了一段代码,该代码接受DataTable数据并插入具有缺失小时值的新行。当表的第一列的值为05:00、07:00、10:00时;执行以下操作后,输出为05:00,06:00,07:00,08:00,09:00,10:00。
我希望这是你正在寻找的:
DataRow row;
DateTime curRowDt, nextRowDt;
object curObj, nextObj; // Used only for conversions
for (int i = 0; i < precip.Rows.Count - 1; ++i)
{
// Resetting id numbers
precip.Rows[i][2] = i;
// Throws exception when changed to
// curRowDt = (DateTime)precip.Rows[i][0];
curObj = precip.Rows[i][0];
nextObj = precip.Rows[i + 1][0];
curRowDt = Convert.ToDateTime(curObj);
nextRowDt = Convert.ToDateTime(nextObj);
if (curRowDt.AddHours(1.0) != nextRowDt)
{
for (int j = 1; j < nextRowDt.Subtract(curRowDt).Hours; ++j)
{
++i;
row = precip.NewRow();
row.ItemArray = new object[] { curRowDt.AddHours(j), null, i };
precip.Rows.InsertAt(row, i);
}
}
}
// Resetting last row's id number
precip.Rows[precip.Rows.Count - 1][2] = precip.Rows.Count - 1;
为了完整起见,这是Mert Erol在接受的答案中提出的调整后的代码。我认为在是否需要身份证号码的问题上存在误解。
DataTable datarow包含一个ItemArray,其中第一列是DateTime,第二列是DataValue。
这是Merl的代码包装成一个函数,我现在正在使用。再次感谢Mert,如果可以的话,我会给你更多的赞!
EDIT: UPDATED用于正确处理数据丢失多天的情况。使用TimeSpan
似乎最适合获得数据点之间的准确小时数。
public DataTable fillMissingHours(DataTable currentDataTable)
{
// iterates through the current DataTable to find missing data and fill it in with
// the correct DateTime and NULL DataValue
DataRow row;
DateTime curRowDt, nextRowDt;
object curObj, nextObj; // Used only for conversions
for (int i = 0; i < currentDataTable.Rows.Count - 1; ++i)
{
curObj = currentDataTable.Rows[i][0];
nextObj = currentDataTable.Rows[i + 1][0];
curRowDt = Convert.ToDateTime(curObj);
nextRowDt = Convert.ToDateTime(nextObj);
if (curRowDt.AddHours(1.0) != nextRowDt)
{
TimeSpan deltaTime = nextRowDt - curRowDt;
for (int j = 1; j < deltaTime.TotalHours; ++j)
{
++i;
row = currentDataTable.NewRow();
row.ItemArray = new object[] { curRowDt.AddHours(j), null };
currentDataTable.Rows.InsertAt(row, i);
}
}
}
return currentDataTable;
}