Missing or mixed time zones

• Each office, device, or server stamped events in its own local time.
• An export dropped the offset and kept only the clock reading.
• One system stores universal time and another stores local time, and their rows were combined.

• Events appear out of order, so a reply can seem to arrive before the message it answers.
• Durations between two timestamps are wrong by whole hours.
• Events near midnight are counted on the wrong day, which moves daily totals.

• Look for offsets or zone names on some values and not on others.
• Compare against a column that should agree, such as a server-side created time. A steady difference of whole hours points to a zone.
• Group by source and look at the hours of the day when activity happens. A source whose working day seems shifted is probably on another clock.

• Find the zone each source records in. This is evidence from outside the file, and no rule can recover it from the clock reading alone.
• Convert every value to one reference, usually universal time, and write the offset explicitly.
• Keep the original local reading in its own column when the local hour matters, such as opening hours.
• Flag values with no zone and no known source. Also flag local times in the repeated hour when clocks go back, since they can mean two different instants.

• Mixed date formatsOne column holds dates written in several different styles.
• Date and time split or mergedSome rows keep the date and the time in separate columns and others pack both into one.
• Dates stored as serial numbersA date appears as a plain number, such as 45292, because it was exported as a count of days or seconds instead of as a calendar date.
• Case example: Marketing analyticsFive clients, five definitions of a campaign

TermsTime zone, Timestamp, ISO 8601, Standardization