How Do I Correct Wrong Canary Historian Timestamps?

Several days of Canary Historian data appear at UTC clock times instead of the intended local clock times. The deciding question is whether the stored timestamps are wrong or the client is merely displaying valid timestamps in the wrong time zone. A display correction preserves the recorded instants; a stored-time correction moves every affected point to a different timestamp.

Timestamp-correction decision

The term timestamp shift here means assigning each value a new timestamp offset from its existing timestamp. Canary can insert a value or overwrite a value at the same exact timestamp, but that operation cannot move an existing value to another time. Moving timestamps requires exporting the affected data and rewriting it with recalculated timestamps.

That rewrite also creates a deletion problem. The Historian cannot delete an arbitrary time range of values. Removal operates at the data-file level. If the original files remain, rewriting shifted points leaves the incorrectly timed points in place and adds another set at the corrected times.

Check 1 — Display time versus stored time

1. Select a known event. Use an event whose real local occurrence time can be established independently, such as a logged operation or controller transition. Expect the Historian point and the independent record to identify the same physical event.
2. Read the point in UTC and local-time views. If changing only the viewer's time-zone interpretation places the event at the correct local clock time, expect no change to the stored data. Correct the display configuration and proceed directly to verification.
3. Compare the recorded instant. If the point represents a different instant and must be moved by an offset, expect an export-and-rewrite operation. Continue to Check 2.

A timestamp is not merely a clock label. It locates a value on a time axis. Converting a correct UTC instant for local display changes the rendered clock time without moving the point. Adding or subtracting an offset changes the instant itself and therefore changes ordering, interval calculations, event alignment, and query results.

Check 2 — Overwrite versus timestamp shift

1. Compare the current and required timestamps. If they are identical, expect a replacement inserted at that exact timestamp to overwrite the existing value.
2. Test the required mapping on a small export. If every affected row needs a different timestamp, expect same-timestamp overwrite to be unusable. Continue to Check 3.
3. Determine the offset for each row. Use the site's intended local-time rules for the date represented by that row. Expect the calculated local clock time to match the independently known event time.

Do not apply one fixed offset across the entire interval until confirming that the applicable UTC offset remained constant. A daylight-saving transition or other local offset change can make the UTC-to-local relationship vary within the affected period. Calculate the mapping from the applicable time-zone rule for each timestamp, then inspect points on both sides of any offset transition. If the local clock repeats an interval, define how duplicate local timestamps will be handled before import.

Check 3 — Data-file removal boundary

1. Identify every Historian data file containing incorrectly timed points. Expect the affected timestamp interval to map to one or more physical files.
2. Inventory all data in those files. Expect that a file may contain valid points outside the bad interval or data that does not require shifting. Because deletion is file-based, removing it removes all data held by that file, not only the selected time range.
3. Expand the export scope to cover everything that must survive file removal. Expect the recovery export to include the bad data for correction and every valid value that must be restored unchanged.
4. Confirm recoverability before deletion. Expect a tested copy of the complete export and a recoverable backup of the affected files. Proceed only after point counts, earliest timestamps, and latest timestamps have been recorded for later comparison.

Deleting only the affected time range is not an available Historian operation. File boundaries therefore control the maintenance scope. Removing a file before exporting its valid contents causes avoidable data loss; retaining it while importing shifted rows causes old and corrected timestamp sets to coexist.

Export, removal, and rewrite procedure

1. Define the exact affected interval using the first and last incorrectly timed points, then identify all data files that contain those points.
2. Export every value that must be restored after those files are removed. Preserve the original export as an immutable recovery copy.
3. Create a working copy of the export. Recalculate timestamps only for affected rows; retain the original timestamps for valid rows sharing the same files.
4. Validate the transformation against known events. Apply the date-specific UTC-to-local mapping and check for duplicate destination timestamps, reversed ordering, gaps, and rows that fall outside the intended interval.
5. Pause or otherwise control writes that could target the affected files during maintenance. Record the maintenance boundary so new data is not lost or mixed into the recovery set.
6. Remove the identified data files through the supported file-level maintenance method. Do not attempt a time-range deletion.
7. Rewrite the complete recovery set: corrected timestamps for affected rows and unchanged timestamps for valid rows removed with the files.
8. Query the rewritten interval before returning it to normal use. If validation fails, stop further writes to the interval and recover from the retained export or file backup.

Verification readings

1. Check 1: known-event alignment. Expect each selected event to appear at its independently established local occurrence time.
2. Check 2: removal of bad timestamps. Expect no values from the affected set to remain at their former incorrect timestamps.
3. Check 3: retained-data restoration. Expect valid data that shared the removed files to have the same timestamps and values recorded before maintenance.
4. Check 4: interval boundaries. Expect values immediately before and after the corrected interval to remain unchanged, with no new gap or overlap at either boundary.
5. Check 5: counts and ordering. Expect the recovered population to reconcile with the export after accounting for any intentionally resolved destination-timestamp collisions, and expect timestamps to follow the intended chronological order.

Frequently asked questions