# Controller Clock Issues and Archiving

Results stored in ToolsNet include a timestamp provided by the tightening controller. If the controller's internal clock is incorrect because of a depleted Real Time Clock (RTC) battery, a failed Network Time Protocol (NTP) synchronization, manual misconfiguration, or clock drift, results may be stored with wrong timestamp. Wrong timestamp can cause a result to be missing from the correct archive or to be archived to the wrong archive database.

Common symptoms:

- Results appear with dates far in the past (for example, 1970 or 2000).

- Results appear with dates far in the future.

- The total number of archived results does not match the number of results in the production database.

## Detecting the Issue

Before you run the archiving job, verify that no results have incorrect timestamps. Run this query against the ToolsNet database to identify suspicious results:

Results with timestamps more than 1 day in the future

`SELECT
r.ID,
r.ResultDateTime,
r.ResultInsertDateTime,
CASE
WHEN u.MasterUnitID IS NOT NULL
AND mu.Name IS NOT NULL
THEN CONCAT(mu.Name, ' - ', u.Name)
ELSE u.Name
END AS ControllerName,
u.IPAddress,
p.Name AS ProgramName,
r.UnifiedResultStatusTypeID AS ResultStatus
FROM AtlasCopco_ToolsNet_Database.ACDC.Result AS r
LEFT JOIN AtlasCopco_ToolsNet_Database.ACDC.Unit AS u
ON r.UnitID = u.ID
LEFT JOIN AtlasCopco_ToolsNet_Database.ACDC.Unit AS mu
ON u.MasterUnitID = mu.ID
LEFT JOIN AtlasCopco_ToolsNet_Database.ACDC.Program AS p
ON r.ProgramID = p.ID
WHERE r.ResultDateTime > DATEADD(DAY, 1, GETUTCDATE())
ORDER BY
r.ResultInsertDateTime DESC;`

Results with timestamps suspiciously old (before ToolsNet deployment, adjust year)

`SELECT
r.ID,
r.ResultDateTime,
r.ResultInsertDateTime,
CASE
WHEN u.MasterUnitID IS NOT NULL
AND mu.Name IS NOT NULL
THEN CONCAT(mu.Name, ' - ', u.Name)
ELSE u.Name
END AS ControllerName,
u.IPAddress,
p.Name AS ProgramName,
r.UnifiedResultStatusTypeID AS ResultStatus
FROM AtlasCopco_ToolsNet_Database.ACDC.Result AS r
LEFT JOIN AtlasCopco_ToolsNet_Database.ACDC.Unit AS u
ON r.UnitID = u.ID
LEFT JOIN AtlasCopco_ToolsNet_Database.ACDC.Unit AS mu
ON u.MasterUnitID = mu.ID
LEFT JOIN AtlasCopco_ToolsNet_Database.ACDC.Program AS p
ON r.ProgramID = p.ID
WHERE
r.ResultDateTime < (
SELECT TOP (1)
TimeStamp
FROM AtlasCopco_ToolsNet_Database.ToolsNet.SystemInfoLog
)
AND r.ResultDateTime < (
SELECT TOP (1)
ResultInsertDateTime
FROM AtlasCopco_ToolsNet_Database.ACDC.Result
ORDER BY
ID
)
ORDER BY
r.ResultInsertDateTime ASC;`

UnitID, name and total results where the year does not match

`SELECT
r.UnitID,
CASE
WHEN u.MasterUnitID IS NOT NULL AND mu.Name IS NOT NULL
THEN CONCAT(mu.Name, ' - ', u.Name)
ELSE u.Name
END AS ControllerName,
u.IPAddress,
COUNT(*) AS MismatchedYearCount
FROM AtlasCopco_ToolsNet_Database.ACDC.Result r
LEFT JOIN AtlasCopco_ToolsNet_Database.ACDC.Unit u
ON r.UnitID = u.ID
LEFT JOIN AtlasCopco_ToolsNet_Database.ACDC.Unit mu
ON u.MasterUnitID = mu.ID
LEFT JOIN AtlasCopco_ToolsNet_Database.ACDC.Program p
ON r.ProgramID = p.ID
WHERE DATEPART(YEAR, r.ResultDateTime) <> DATEPART(YEAR, r.ResultInsertDateTime)
GROUP BY
r.UnitID,
CASE
WHEN u.MasterUnitID IS NOT NULL AND mu.Name IS NOT NULL
THEN CONCAT(mu.Name, ' - ', u.Name)
ELSE u.Name
END,
u.IPAddress
ORDER BY MismatchedYearCount DESC;`

If the query returns results, record the ControllerName and IP Address, and investigate the controller clock before running the archiving job.

## Impact on Archiving

The archiving process selects results using the Time Stamp column. If a controller sends results with incorrect timestamp, archiving behavior changes as described below.

Scenario | Archiving impact
--- | ---
Timestamp in the past (for example, year 2000) | Results may be archived immediately on the first run to the wrong archive, even if they are recent operational results. They will follow the archive retention policy for that archive.
Timestamp in the future (for example, year 2099) | Results will not be archived until that future date is reached. This causes the production database to grow.
Mixed correct and incorrect timestamps on the same controller | The “latest result” for an assembly task may be wrong. This affects SPC, statistics, and reporting.
Timestamp in the wrong year | Results may be archived to the wrong archive database.

## Recommended Actions Before Archiving

- Check the controller RTC battery and replace it if depleted.

- Make sure the controller is configured to sync time with a reliable NTP server and that sync is active.

- Correct any manual time misconfiguration on the controller.

- Do not run the archiving job for controllers that have submitted results with incorrect timestamps.

- For manual updates to timestamps in ToolsNet, open a QCM support ticket.

- If a large volume of results has incorrect timestamps and archiving cannot proceed safely, inform the ACDC/ToolsNet support team.

ToolsNet 8 will not detect or correct incorrect controller timestamps automatically. This is by design. Result timestamp integrity is the responsibility of the controller.

## Recommended Actions After Archiving

Run a query for the specified date range (2024-01-01 to 2025-01-01) to find production results that were not archived. The query should:

- Compare each production result ID (Result.ID) with the archive reference (Archive.dbo.Result.ResultID).

- Return only records with no matching archive entry.

- Include unit and controller details (show MasterUnitName - UnitName when applicable), the unit IP address, and the full result record to support investigation of missing archive records.

`SELECT
u.ID AS UnitID,
CASE
WHEN u.MasterUnitID IS NOT NULL AND mu.Name IS NOT NULL
THEN CONCAT(mu.Name, ' - ', u.Name)
ELSE u.Name
END AS ControllerName,
u.IPAddress AS IPaddress,
r.*
FROM AtlasCopco_ToolsNet_Database.ACDC.Result r
INNER JOIN AtlasCopco_ToolsNet_Database.ACDC.Unit u
ON r.UnitID = u.ID
LEFT JOIN AtlasCopco_ToolsNet_Database.ACDC.Unit mu
ON u.MasterUnitID = mu.ID
LEFT JOIN Archive.dbo.Result arch
ON r.ID = arch.ResultID
WHERE
r.ResultDateTime >= '2024-01-01'
AND r.ResultDateTime < '2025-01-01'
AND arch.ResultID IS NULL
ORDER BY
ControllerName,
r.ResultDateTime DESC;`

In Archive.dbo.Result , ensure that Archive is replaced with your environment’s archive database name. For example, if your archive database is named ToolsNetArchive, update it to ToolsNetArchive.dbo.Result.