How to Correct Logged Data for devices with Incorrect Timestamp in Power Monitoring Expert (PME)

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

Struxureware Power Monitoring 7.0(SPM)

Power Monitoring Expert 7.2.x

Power Monitoring Expert 8.x

Power Monitoring Expert 9.0

Environment
SQL Server Management Studio, ION setup, PME

Cause
The meter was time synched with an incorrect time. This resulted in the meter recording data logs with incorrect timestamp. 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 timestamp 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, 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:

• The above query displays the records where the timestamps are incorrect.

• 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:

• Run the previous select statement to confirm that the timestamps have been corrected.