Skip to content

Systems and records

How to audit dataset timestamps before reconstructing workflows

By SourceX Editorial · Updated

10 sections

Short answer

Audit timestamps in three passes before rebuilding workflows from them. Name what each field records, who wrote it and how it was stored. Run fixed checks for validity, range, event order, clustering, time zone and daylight saving behavior, and agreement across systems. Log every exception with a count, cause and decision, and keep repaired values in separate fields so buyers can reproduce the result.

Checklist

Audit dataset timestamps

This page covers how to audit dataset timestamps before reconstructing workflows.Illustrative

Key takeaways

  • Start with a field inventory: the event each timestamp claims to record, its writer and its storage zone.
  • An import date that replaced the original event time is the defect most likely to fail acceptance while every file opens.
  • Hour-of-day profiles reveal local times stored as UTC; a one-hour jump at daylight saving transitions confirms it.
  • Convert with named IANA zones, not fixed offsets, so historical rules apply on each record's date.
  • Never overwrite originals: put repaired times in separate fields with the method and second source.

How do you audit timestamps before reconstructing business workflows?

Audit timestamps in three passes before anyone rebuilds a workflow from them. First, write down what each timestamp field records, who or what wrote it and how it was stored. Second, run a fixed set of consistency checks: valid values, plausible ranges, event order, clustering, time zone and daylight saving behavior, and agreement across systems. Third, log every exception with a count, a cause and a decision.

The reason to do this before reconstruction is that workflow order is built from timestamps. If a ticket's created time is really its import time, or a work order's completion time was stored in local time while the dispatch time was stored in UTC, the rebuilt sequence will be wrong in ways that look perfectly normal. Files open, columns parse, and the steps still come out in the wrong order.

The audit below is designed for operational exports such as helpdesk tickets, CRM activities, field service jobs and ERP transactions. It is a data preparation method, and the checks are written so an operations analyst with SQL or a spreadsheet can run them.

What does each timestamp field actually record?

Each timestamp field records one specific event as seen by one specific writer, and the audit starts by naming both. A created_at field may mean when a customer sent a message, when an agent logged a call, when a nightly sync copied the record or when a migration script inserted it.

Storage type decides what can still be recovered. In PostgreSQL, a timestamp with time zone value is converted to UTC on input and stored as UTC, and the originally stated or assumed zone is not retained; if the input string carries no zone, the server's TimeZone setting is assumed. For a timestamp without time zone, PostgreSQL silently ignores any zone indication in the input. Apache Parquet draws the same line with its isAdjustedToUTC flag: values normalized to UTC identify a single instant, while local values do not identify an instant without additional information such as an offset or zone.

Fill in one row per timestamp field before running any check. The template below is the first half of the audit.

What does each timestamp field actually record?
FieldEvent it claims to recordWriterStorage and zoneHow you confirmed it
ticket.created_atCustomer's first message receivedSystem on intake; import script for migrated ticketsUTC string with Z suffix in the API exportVendor API reference and five tickets checked against email headers
ticket.solved_atAgent set status to solvedSystem on status changeUTC, derived from the event logEvent log entries for a sample of tickets
job.scheduled_startAppointment window startDispatcherLocal wall time, no offset storedDispatcher interview and screen comparison
job.completed_atTechnician closed the job on a mobile deviceDevice clock, synced on uploadUnknown; to be testedHour-of-day profile pending
invoice.posted_dateAccounting period postingFinance user or batch jobDate only, no timeERP field definition

The timestamp consistency and timezone audit

The audit is a fixed list of checks, each with a rule you can run and a threshold that triggers investigation. Run every check on every timestamp field, record the count of records flagged, and do not repair anything until all checks have run, because one cause often explains several flags.

At a glance

The timestamp consistency and timezone audit

Thresholds depend on the dataset; set them before running and write them in the report so a buyer can see what counted as a defect.

The timestamp consistency and timezone audit
CheckRule to runFlag ifLikely causeDefault decision
T1 Parse validityParse every value with an explicit format and zone ruleAny value fails to parse or parses to a different field orderMixed formats, day and month swappedFix parser; never guess per row
T2 Sentinel datesCount values at epoch starts, spreadsheet day zero and year-end placeholdersAny cluster on a single placeholder valueBlank fields filled with defaultsSet to null and flag
T3 RangeCompare against the system go-live date and the export dateValues before go-live or after exportImports, device clocks, typosFlag; repair only from a second source
T4 Event orderCheck that each later step is not earlier than the step before itClosed before created, completed before scheduledZone mismatch between fields, backfillsInvestigate zone first
T5 ClusteringCount records sharing one timestamp or one dateSpikes far above the daily normMigration import date, bulk closure jobsFlag as derived; recover originals if kept
T6 Hour-of-day profilePlot activity by hour for each field and teamBusiness-hours peak shifted by whole hoursLocal time stored or labeled as UTCRe-interpret with the correct zone
T7 Daylight savingFind local times inside spring gaps and fall repeatsNonexistent or ambiguous local timesLocal wall time stored without offsetResolve with zone rules; flag ambiguous
T8 Cross-system driftJoin the same event across two systems and compare timesConstant or seasonal offset between systemsDifferent zones or unsynced clocksNormalize; document the offset
T9 Precision and tiesMeasure share of identical timestamps within a caseMany steps share a time after a given dateTruncation to minutes or days in migrationOrder by sequence ID; flag ties
T10 Zone metadataCheck whether each value carries an offset or zoneOffsets missing or inconsistent across rowsExport tool defaultsRecord the rule used to assign zones

How do you tell a time zone error from a real pattern?

A time zone error shifts activity by whole hours in a way that tracks the zone's rules, while a real pattern does not. Plot when work happens by hour of day for each field. A support team that works nine to five in Chicago should show a peak in those hours once times are converted correctly; if the peak sits five or six hours earlier, the times were probably local values stored or labeled as UTC.

Daylight saving makes the signature sharper. If the apparent shift changes by one hour at the spring and fall transitions, the values were written in local wall time. Local wall time also produces two impossible cases: times inside the spring gap that never existed, and times in the fall hour that occurred twice. Python's datetime documentation describes the fold attribute for exactly this case: it disambiguates wall times during a repeated interval, which occurs when clocks are rolled back at the end of daylight saving time or when a zone's UTC offset is decreased.

Run the profile separately for each team, site and source system rather than once for the whole export. A company with offices in two zones will show two peaks in a combined chart, which can look like a shift pattern when it is really two clocks. Splitting the profile also exposes the case where one system was configured with the wrong account time zone for a period and then corrected, which shows up as a step change on a specific date rather than a seasonal pattern.

Use the IANA time zone database for conversions rather than fixed offsets. The tz project describes its database as an attempt to record the history and predicted future of civil time scales, organized into zones whose clocks have agreed since 1970, and it warns that predictions for future timestamps become wrong when governments change the rules. For historical records, that means a conversion should use the rules in force on the record's date, which a named zone such as America/Chicago provides and a fixed offset does not.

Which timestamp defect slips past every parser but fails a buyer's review?

The defect most likely to change a buyer's acceptance decision while every file opens cleanly is an import date that replaced the original event time. It turns years of history into a single day, destroys durations and makes order between old records meaningless, yet each value is a valid timestamp.

Systems allow this for good reasons. Zendesk's Tickets API documents created_at as read-only in normal use, with sample values in UTC, but its separate Ticket Import endpoint lets an admin set created_at, updated_at and solved_at when bringing in legacy tickets. Whether a past migration used that option decides whether the created times are original or the day of the import. Check T5 finds it: look for a spike in created dates and compare the oldest records against a second source such as email headers or the legacy system's export.

The second most consequential defect is mixed zones between fields of the same record. A workflow reconstruction that compares a UTC creation time with a local completion time produces negative or inflated durations that look like real delays. Check T4 catches the negative cases, but inflated durations pass every order check, which is why the hour-of-day profile in T6 matters.

Spreadsheet and text exports: date systems and offset notation

Spreadsheet exports store dates as serial day numbers, and the date system decides which day a number means. Microsoft documents that Excel's 1900 date system counts from January 1, 1900 and the 1904 system from January 1, 1904, and that the same date's serial value differs by 1,462 days between them, so copying dates between workbooks with different systems shifts them by about four years. The 1900 system also treats 1900 as a leap year, which affects serial values in early 1900.

Text exports carry their own trap in offset notation. RFC 3339 defines the Internet timestamp profile of ISO 8601 and gave -00:00 a special meaning: the time is known in UTC but the local offset is unknown. RFC 9557 later updated that convention so that Z now carries that meaning, while +00:00 still indicates that UTC is the preferred reference. In practice, a Z suffix tells you the instant but not the local time the person experienced, which matters when you reconstruct business hours or shift patterns.

Add two checks when spreadsheets or text files are in the chain: confirm the workbook date system before converting serials, and record whether offsets in text exports mean a local offset or only a UTC instant.

Illustrative worked example: a helpdesk and field service history

Illustrative example, invented to show the method: a property maintenance company prepares five years of helpdesk tickets and field service jobs so buyers can study request-to-repair workflows. The helpdesk was migrated from an older tool in its second year.

The field inventory shows ticket times in UTC and job times as local wall time without offsets. T5 flags a spike: about a fifth of all tickets share one created date, the migration day. The legacy export, kept in a shared drive, still holds original dates for most of them, so they are restored into a separate field marked as recovered from the legacy export, and the rest are flagged. T6 shows job completions, once converted from UTC to the company's zone, peaking before dawn while technicians work days, consistent with local times read as UTC; the team re-interprets job times with the company's named zone. T7 then finds a few dozen completions in the repeated fall hour, which are marked ambiguous rather than guessed. T8 compares ticket solved times with job completion times and finds a remaining constant offset in one region, traced to a second branch office in another zone.

The team also finds that the mobile app recorded completion times to the minute while the helpdesk kept seconds, so several job steps share a minute. Rather than inventing an order, it uses the job's step sequence number to order ties and flags the cases where no sequence exists.

After repair, the company offers the four years where both systems reconcile and documents the first year as partial. Every count in this example is invented for illustration; it does not describe a real company or dataset.

What should the audit report and exception log contain?

The audit report should let a buyer reproduce every decision without asking you. Keep original values untouched and put repaired or re-interpreted times in separate fields with a source note, so a reviewer can choose which to trust.

At a glance

What should the audit report and exception log contain

  • Field inventory: every timestamp field with its event, writer, storage type and zone rule.
  • Checks run: T1 to T10 with the rule, threshold, records tested and records flagged.
  • Causes found: each cause with the checks it explains and the periods or teams affected.
  • Repairs: method, second source used, number of values changed, and the name of the derived field.
  • Unrecoverable values: counts by field and period, and whether they are nulled, flagged or excluded.
  • Zone rules: the named zones used per site or team and the date each applied from.
  • Exception log: one row per exception class with an owner and a decision.
  • Version and date of the audit, and the export it was run against.

When can the order of events not be trusted at all?

Stop and narrow the scope when the order of events cannot be rebuilt from any field: no trustworthy timestamps, no sequence numbers and no second system that saw the same events. A workflow dataset without reliable order is a collection of records, and it should be described that way.

Narrow rather than repair when defects are concentrated in identifiable periods, such as the months around a migration or a time before a system was configured. Offering the clean years with a documented boundary is usually stronger than offering everything with caveats.

Request specialist review when records span several jurisdictions or many sites with different zones, when device clocks wrote critical times, when de-identification shifted or generalized dates, or when the reconstructed durations will support decisions about people, such as performance or service-level disputes. Date shifting in particular changes intervals and order across records, so its method must be documented alongside the timestamp audit.

Where a timestamp audit fits in a SourceX licensing deal

In a SourceX deal, which spans sourcing and qualification, rights review, contracting, data preparation, delivery and payment, a timestamp audit belongs to the preparation stage, and its report becomes part of what a buyer reviews before accepting the records.

Order matters more as history grows. The span SourceX's general guide describes buyers as wanting, two or more years of consistent records and tens of thousands of them in one system, is exactly where time zones, daylight saving rules and migrations pile up; each deal is still judged individually. Multi-terabyte event histories do not have to be hosted by SourceX: they can remain in your own storage or travel on encrypted drives.

No timestamps or files are needed for the first step either: SourceX opens with a brief assessment of the company.

Frequently asked questions

What should the timestamp consistency and timezone audit include?

It should include a field inventory naming what each timestamp records, who wrote it and how it was stored, then ten checks: parse validity, sentinel dates, range, event order, clustering, hour-of-day profile, daylight saving, cross-system drift, precision and zone metadata. Each check records the rule, threshold and flagged count, followed by an exception log with decisions.

Should timestamps in a dataset be delivered in UTC?

UTC is the safest representation of the instant, but it loses the local time a person experienced. Deliver UTC values plus the named time zone or original offset where business hours, shifts or local deadlines matter, and say which fields were stored in local time.

How can I tell if a migration overwrote created dates?

Count records by created date and look for a spike far above the daily norm, usually on the migration day. Then compare a sample of those records with a second source, such as email headers or the legacy system's export, to see whether original dates survive.

What should I do with ambiguous times in the daylight saving fall-back hour?

Do not guess. If another field or a sequence number orders the events, use it to choose the earlier or later occurrence; otherwise mark the value as ambiguous and keep it out of duration calculations.

Is it acceptable to repair timestamps before licensing records?

Yes, if the original values stay untouched, repaired values sit in a separate field, and the method and second source are documented. Buyers can then decide which values to trust for their purpose.

Sources

  • PostgreSQL stores timestamp with time zone values as UTC, assumes the TimeZone setting when input has no zone, does not retain the original zone, and ignores zone indications for timestamp without time zone. Source
  • The Parquet format specification says UTC-normalized timestamps identify a single instant, while local timestamps do not without an offset or time zone. Source
  • Python's datetime documentation describes the fold attribute for disambiguating wall times during a repeated interval such as the end of daylight saving time. Source
  • The tz project describes its database as recording the history and predicted future of civil time, with zones whose clocks agree since 1970, and warns that predictions break when governments change rules. Source
  • RFC 3339 used the -00:00 offset to mean a known UTC time with an unknown local offset, distinct from Z or +00:00. Source
  • RFC 9557 updates RFC 3339 so that Z now carries the unknown local offset meaning, while +00:00 keeps its meaning. Source
  • Microsoft documents that Excel's 1900 and 1904 date systems give the same date serial values 1,462 days apart, and the same article notes that the 1900 system treats 1900 as a leap year. Source
  • Zendesk's Tickets API documents created_at as read-only, with UTC example values. Source
  • Zendesk's Ticket Import endpoint lets an admin set created_at, updated_at and solved_at on imported legacy tickets. Source

See if your company qualifies

A short company assessment. No data uploads are needed.

See if your company qualifies