private void AdjustFileForSpringDst(AgdFile agdFile, IDbConnection db, DaylightTime daylightTime) { //see if there's any epochs before DST int epochsBeforeSpringDst = db.Scalar<int>( db.From<AgdTableTimestampAxis1>() .Select(Sql.Count("*")) .Where(q => q.TimestampTicks < daylightTime.Start.Ticks)); bool dataBeforeSpringDst = epochsBeforeSpringDst > 0; int epochsAfterSpringDst = db.Scalar<int>( db.From<AgdTableTimestampAxis1>() .Select(Sql.Count("*")) .Where(q => q.TimestampTicks > daylightTime.Start.Ticks)); bool dataAfterSpringDst = epochsAfterSpringDst > 0; if (!dataBeforeSpringDst && dataAfterSpringDst) { //no need to do anything richTextBox1.AppendText(string.Format("{0}: NOT adjusting file for spring DST\r\n", agdFile)); return; } richTextBox1.AppendText(string.Format("{0}: adjusting file for spring DST\r\n", agdFile)); //adjust timestamps for data after DST by subtracting an hour string sql = string.Format( "UPDATE data SET dataTimestamp = dataTimestamp + {0} WHERE dataTimestamp >= {1}", daylightTime.Delta.Ticks, daylightTime.Start.Ticks); db.ExecuteSql(sql); //add the extra hour of data (October 25th 0200) var totalEpochsToInsert = daylightTime.Delta.TotalSeconds / agdFile.EpochLengthInSeconds; var ticksPerEpoch = TimeSpan.FromSeconds(agdFile.EpochLengthInSeconds).Ticks; for (int i = 0; i < totalEpochsToInsert; i++) { sql = string.Format("insert into data(dataTimestamp) values ({0});", (daylightTime.Start.Ticks + (i * ticksPerEpoch))); db.ExecuteSql(sql); } var columnNames = db.GetColumnNames("data"); foreach (var columnName in columnNames) { sql = string.Format("UPDATE data SET {0} = 0 WHERE {0} IS NULL", columnName); db.ExecuteSql(sql); } //adjust WTV if (db.TableExists("filters")) { sql = string.Format( "UPDATE filters SET filterStartTimestamp = filterStartTimestamp + {0} WHERE filterStartTimestamp >= {1}", daylightTime.Delta.Ticks, daylightTime.Start.Ticks); db.ExecuteSql(sql); sql = string.Format( "UPDATE filters SET filterStopTimestamp = filterStopTimestamp + {0} WHERE filterStopTimestamp >= {1}", daylightTime.Delta.Ticks, daylightTime.Start.Ticks); db.ExecuteSql(sql); } if (db.TableExists("wtvBouts")) { sql = string.Format( "UPDATE wtvBouts SET startTicks = startTicks + {0} WHERE startTicks >= {1}", daylightTime.Delta.Ticks, daylightTime.Start.Ticks); db.ExecuteSql(sql); sql = string.Format( "UPDATE wtvBouts SET stopTicks = stopTicks + {0} WHERE stopTicks >= {1}", daylightTime.Delta.Ticks, daylightTime.Start.Ticks); db.ExecuteSql(sql); } //adjust capsense if (db.TableExists("capsense")) { //adjust timestamps for data after DST by subtracting an hour sql = string.Format( "UPDATE capsense SET timeStamp = timeStamp + {0} WHERE timeStamp >= {1}", daylightTime.Delta.Ticks, daylightTime.Start.Ticks); db.ExecuteSql(sql); } }