Why Date-Time Harmonization Matters
A recorded clock time is not always a uniquely defined point in time. The string “08:30” might be a local reading in a clinic, a reading in UTC, or a value exported by a system that has already applied a time-zone conversion. Even a complete local date and clock time may be ambiguous during a daylight-saving transition. If dosing and sampling records are combined without resolving these distinctions, their apparent ordering or timing can be wrong.
For population pharmacokinetic data construction, the goal is not to make every source value look identical by overwriting it. The goal is to create a consistent, auditable representation while retaining the information as it was received. This tutorial focuses on date-time representation and conversion; it does not calculate elapsed time between visits or events.
A Standardized Representation
A useful target is to store the original source string unchanged and, where the source supplies enough information, also store a parsed timestamp normalized to UTC. For example, the ISO 8601 string 2026-01-15T08:30:00-05:00 includes a date, clock time, and offset. The trailing -05:00 says that local time is five hours behind UTC. A corresponding normalized representation is 2026-01-15T13:30:00Z, where Z denotes UTC.
Keep the source value even after parsing and conversion. A practical data design distinguishes the raw source string, the parsed local date and time, any supplied offset or time-zone identifier, the normalized UTC timestamp (if determinable), and the precision of the record. Record whether the zone came from the source, an operational specification, or an explicit documented assumption. Do not replace missing zone information with a guess and then treat the result as observed fact.
For a known offset, define the offset as local time minus UTC. Then the conversion is:
The arithmetic is straightforward once the applicable offset is known. Establishing that offset is the part that requires care. A time-zone identifier can be more informative than a fixed offset because its rules may change over the calendar. Abbreviations such as “EST” or “CST” are not reliable substitutes for a geographic time-zone identifier: abbreviations can be ambiguous, and a standard-time abbreviation does not by itself describe daylight-saving rules.
Worked Example: Converting Timestamps With a Known Offset
Suppose a hypothetical clinic sends two sample collection timestamps from a system configured for America/New_York. The records are 2026-01-15 08:30 and 2026-07-15 08:30. Assume the source clock readings are local times and the applicable time-zone rules are confirmed.
On January 15, the applicable offset is UTC−05:00. Subtracting a negative five-hour offset means adding five hours:
The normalized timestamp is 2026-01-15T13:30:00Z. On July 15, the applicable offset is UTC−04:00, so:
The normalized timestamp is 2026-07-15T12:30:00Z. Although both local readings say 08:30, their UTC times differ because the applicable offsets differ. These conversions are justified only because the location and the interpretation of the source readings are known. Retain both original local values and the conversion metadata.
Daylight-Saving Gaps and Repeated Times
Time-zone rules can make a local clock jump forward or backward. When clocks jump forward, some local clock readings do not occur. When clocks move backward, some readings occur twice. These are different data problems: a nonexistent time cannot be converted as an actual local instant, while a repeated time has more than one possible UTC interpretation.
In the United States, for example, the hypothetical local time 2026-03-08 02:30 in America/New_York falls in the spring-forward gap: the clock advances from 01:59:59 to 03:00:00, so 02:30 does not occur under that zone’s rules. By contrast, on the autumn transition, 2026-11-01 01:30 occurs twice in that zone, once under UTC−04:00 and once under UTC−05:00. A local date and clock reading alone therefore cannot select a unique instant.
Worked Example: Handling an Ambiguous Clock Reading
A hypothetical source record reports a collection at 2026-11-01 01:30 in America/New_York, but supplies neither an offset nor an indication of which occurrence was intended. The applicable local time is repeated during the autumn clock change.
If the first occurrence, with offset UTC−04:00, was intended, the conversion is:
If the second occurrence, with offset UTC−05:00, was intended, the conversion is:
The source value alone supports two candidate instants, an hour apart. Do not choose one silently. Seek an offset, a more detailed source record, or an authoritative study-specific clarification. If no evidence resolves the ambiguity, preserve the local value, flag it as ambiguous, and leave the normalized instant unresolved rather than presenting either candidate as certain.
A common but unsafe shortcut is to apply the offset that is usual for a site or to let software select a default interpretation. That can produce a well-formed timestamp without producing a justified one. Conversion software should be configured to identify nonexistent and ambiguous local times rather than silently shifting or selecting them.
Date-Only Records Are Not Timestamps
Some records contain a date but no clock time. A date-only value may be an accurate representation of what the source recorded; it is not automatically an incomplete timestamp that should be completed with midnight. The meaning of the date also needs to be clear: it might be a local calendar date at a site, a date in a central system, or a date already expressed in UTC.
Worked Example: Preserving a Date-Only Sample Record
A hypothetical laboratory record lists the collection date as 2026-05-04 and provides no time. Assume the date refers to the local calendar date in America/New_York, where the offset is UTC−04:00 on that date. The record establishes the date, but not when during that date the sample was collected.
The local calendar day begins at midnight local time. That boundary corresponds to 04:00 UTC:
The next local midnight is 2026-05-05 04:00 UTC. Thus, if the local-date interpretation and zone are confirmed, the date identifies a range of possible instants from 2026-05-04 04:00 UTC up to, but not including, 2026-05-05 04:00 UTC. It does not identify a point within that range.
Store the value as date-only, with its source and date interpretation documented. Do not label either boundary—or a midpoint—as the observed collection timestamp. If a downstream process technically requires a timestamp, any placeholder convention must be explicitly marked as a processing convention and must not be mistaken for measured event time.
The same principle applies when the date’s time-zone meaning is unknown. A date string by itself does not specify which UTC interval it represents. Do not infer a site zone merely from the participant’s country or from a preferred system setting unless an authoritative source establishes that interpretation.
A Reproducible Harmonization Process
Apply a consistent process to every source rather than making case-by-case edits that cannot be audited later. The following steps separate parsing from interpretation and conversion:
Keep the original string or date value unchanged. Preserve enough source information to identify its system, field, and intended meaning.
Identify the ordering of year, month, and day, the clock format, and whether the value is a full timestamp or date-only. Do not assume that an ambiguous string such as 04/05/2026 means the same thing in every system.
Determine whether the clock reading is UTC or local, and identify the applicable offset or geographic time-zone rule. Record the evidence or assumption supporting that interpretation.
Test local timestamps against the applicable rules. Flag nonexistent times and repeated times unless the source provides enough information to resolve them.
For an unambiguous timestamp, store the UTC equivalent and retain the source value and metadata. Keep date-only values and unresolved local times identifiable as such.
Check parsing failures, missing zones, unexpected conversions, and records that do not conform to the source system’s documented format. Resolve or flag exceptions before treating normalized values as definitive.
Checks, Alternatives, and Limitations
A standardized UTC representation supports comparison across systems and sites, but it does not repair uncertain source information. Use targeted checks to detect when the conversion process or its inputs are unreliable:
- Count values that fail the documented date-time format or contain impossible calendar dates.
- Identify local timestamps that fall in a daylight-saving gap or repeated interval.
- Review records that have a clock time but no known time basis, and date-only values that have been converted to point timestamps.
- Compare source strings with parsed fields and converted values for a sample of records, including dates on both sides of clock transitions.
- Check that the recorded conversion assumptions match the source system and site documentation; do not infer that a plausible-looking timestamp is necessarily correct.
There are several reasonable storage choices, but they are not interchangeable. Keeping only the original string preserves the source representation but makes consistent cross-system interpretation difficult. Keeping only UTC simplifies instant comparisons but loses the original clock reading and can hide how a conversion was made. Storing both the raw value and a normalized value, with zone and conversion status, supports both auditability and consistent processing. For date-only values or unresolved local timestamps, retaining a non-instant representation is more accurate than forcing a UTC value.
A further limitation is that time-zone rules can change, and historical rules may differ from current rules. A conversion should use the rule set appropriate to the recorded date, with the rule source or software version documented when reproducibility requires it. If a source supplies an explicit numeric offset, preserve it; do not silently replace it with a location-based offset that conflicts with the supplied value. Investigate that conflict instead.
Check Your Understanding
Answer each question, then compare your reasoning with the solutions.
- A hypothetical source gives a confirmed local time of 14:10 at a site with offset UTC−05:00 on that date. What is the UTC time?
- A source gives 2026-11-01 01:30 in America/New_York without an offset. Why is one UTC conversion not justified by this information alone?
- A record contains only the local collection date 2026-05-04 in America/New_York. Should midnight UTC be stored as the collection time? Explain.
- A hypothetical timestamp is 2026-07-10 09:00 in Asia/Tokyo, with the source’s local-time interpretation confirmed and offset UTC+09:00. Convert it to UTC.
Solutions. (1) Using UTC = local time − offset, \(14{:}10-(-05{:}00)=19{:}10\) UTC. (2) The local clock reading occurs twice on that autumn transition: the UTC−04:00 occurrence is 05:30 UTC and the UTC−05:00 occurrence is 06:30 UTC. The source needs an offset or other authoritative disambiguation. (3) No. The value establishes a local calendar date, not an instant. If its local-date meaning and zone are confirmed, it corresponds to the interval from 2026-05-04 04:00 UTC up to 2026-05-05 04:00 UTC; neither boundary is an observed collection time. (4) \(09{:}00-(+09{:}00)=00{:}00\) UTC on 2026-07-10. Preserve the original local timestamp and the offset used for conversion.