{}

Our Brands

Impact-Company-Logo-English Black-01-177x54

Schneider Electric USA Website

Welcome to our website.
How can we help you today?
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.


SQL Table Results TimestampUTC

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:
DateAdd SQL command

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

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.

Schneider Electric USA

Explore more
Range:
Articles that might be helpful Users group

Discuss this topic with experts

Visit our Community for first-hand insights from experts and peers on this topic and more.
Explore more
Range: