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:
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.
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:
FROM DataLog
INNER JOIN DataLogStamp ON DataLog.DataLogStampId = DataLogStamp.ID
- Run the previous select statement to confirm that the timestamps have been corrected.