Technical FAQs

Ask a Question

How to Correct Logged Data for devices with Incorrect Timestamps

Issue
A meter was time synched with an incorrect time, resulting in meter logging data with the wrong time for an unknown duration.

Product line
ION Enterprise 5.5/6, SPM 7.0.1, PME 7.2 and PME 8

Environment
SQL Server Management Studio, ION setup

Cause
The meter was time synched with an incorrect time. This resulted in the meter recording data logs with incorrect timestamps. The effected date range was identified by comparing the data obtained directly from the meter using ION Setup and the datalog tables in vista.

Resolution
Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.* 

The following procedure can correct logged data with incorrect timestamps in an ION Enterprise/ PME SQL database.
In the following example, all of the logs between 2015-06-01 and 2015-08-01 were logged with a timestamp that was 4 hours ahead of the actual time. Query the database against the period where there is a problem:


First, let’s identify the source ID by running the following query on the ION_Data database

Select ID, Name, Signature from Source

The query will return the following in this example:

ID Name Signature
4 MATT.ION7500RTU PK-0401A206-01

For SPM 7.0.1 and later:

1- Use the SourceID to query the DataLog2 table for the records in the range of the timestamps that are incorrect.




Select ID, TimestampUTC From datalog2 WHERE SourceID=4 and timestamputc>'2015-06-01' and timestamputc<'2015-08-01' order by timestamputc desc

After verifying the incorrect timestamp, update the DataLog2 table by offsetting the TimeStampUTC value to the correct timestamp by running the following query:

Update DataLog2 Set TimestampUTC = DATEADD(hour, -4,timestamputc) from DataLog2 where SourceID=4 and timestamputc>'2015-06-01' and timestamputc<'2015-08-01'
 
The DateAdd functionality works for the following dateparts:


Once the query is successfully executed, verify that the timestamps have been modified by running the select functionalities

 
For ION enterprise 6.0 and below:

  • Use the SourceID to query the DataLogStamp table for the records in the range of the timestamps that are incorrect.
  • In the following example, some of the logs between 2004-08-27 and 2004-08-24 were logged with a timestamp that was 12 hours ahead of the actual time. Query the database against the period where there is a problem:
SELECT distinct datalogstampid, timestampUTC
FROM DataLog
INNER JOIN DataLogStamp ON DataLog.DataLogStampId = DataLogStamp.ID
WHERE DataLogStamp.SourceID=4 and timestamputc>'2004-08-24' and timestamputc<'2004-08-27' order by datalogstampid
  • The above query displays the records where the timestamps are incorrect.
NOTE: DataLogStampIDs 71614 and 71619.
 
DataLogStampID TimestampUTC
71583 2004-08-25 01:15:00.000
71614 2004-08-25 13:30:00.000
71615 2004-08-25 13:45:00.000
71616 2004-08-25 14:00:00.000
71617 2004-08-25 14:15:00.000
71618 2004-08-25 14:30:00.000
71619 2004-08-25 14:45:00.000
71620 2004-08-25 03:00:00.000
  • Use these IDs to specify the range with which to update the recorders. Update the DataLogStamp table by offsetting the TimeStampUTC value to the correct timestamp:
UPDATE DataLogStamp SET TimestampUTC = DATEADD(hour,-12,timestampUTC)
FROM DataLog
INNER JOIN DataLogStamp ON DataLog.DataLogStampId = DataLogStamp.ID
  • Run the previous select statement to confirm that the timestamps have been corrected.
Was this helpful?
What can we do to improve the information ?