Tutorial: The Five Timestamp Formats in a Tracking Stack and How to Convert Between Them

A tracking event can appear as Unix seconds, milliseconds, microseconds or text with a UTC offset. An offset states the difference from UTC; +02:00 means two hours ahead. At 12:00 UTC, a clock with the +02:00 offset shows 14:00. Reports also store calendar dates. Reliable conversion requires three facts: the unit, the meaning of the field and the intended time zone.

Browser time, container start, event time and receipt time do not automatically describe the same instant. Comparing numbers only makes sense after comparing those meanings. Digit count provides a clue, not a field definition.

1. Five common representations

The examples below refer to 2026-09-26T23:30:00Z, which is 01:30 on 27 September 2026 in Berlin. Only the first three formats count units from the Unix epoch, 1 January 1970 at 00:00 UTC. ISO notation expresses calendar fields; a date alone does not identify an instant.

Date.now() returns milliseconds from the current system clock. In the standard GTM installation, gtm.start records container initialization, not every subsequent click. A correct unit can therefore coexist with an incorrect interpretation.

For values from 2026, ten, 13 and 16 digits are typical of seconds, milliseconds and microseconds. Older or negative timestamps, other epochs and ordinary identifiers do not fit this recognition rule. Eight digits also do not establish a valid date: 20260230 is not a calendar day.

2. Conversion with an explicit unit

The function accepts integers or strings of decimal digits with an optional sign. Value and unit are separate arguments. Floating-point numbers, booleans and decimal-fraction strings are rejected to prevent unnoticed rounding. Nanoseconds are accepted only when conversion to microseconds is exact.

import re
from datetime import datetime, timedelta, timezone

EPOCH = datetime(1970, 1, 1, tzinfo=timezone.utc)

def epoch_to_utc(value, unit):
    if isinstance(value, bool):
        raise TypeError("boolean is not a timestamp")
    if isinstance(value, str):
        if not re.fullmatch(r"[+-]?[0-9]{1,20}", value):
            raise ValueError("expected an integer string")
        value = int(value)
    elif not isinstance(value, int):
        raise TypeError("expected an integer or integer string")
    factors = {"s": 1_000_000, "ms": 1_000, "us": 1}
    if unit == "ns":
        micros, remainder = divmod(value, 1_000)
        if remainder:
            raise ValueError("nanoseconds would lose precision")
    elif unit in factors:
        micros = value * factors[unit]
    else:
        raise ValueError("unit must be s, ms, us or ns")
    try:
        return EPOCH + timedelta(microseconds=micros)
    except OverflowError as exc:
        raise ValueError("outside Python datetime range") from exc

print(epoch_to_utc("1790465400000000", "us").isoformat())
# 2026-09-26T23:30:00+00:00

The result is a UTC datetime with microsecond resolution. The function checks type, unit and Python’s representable range, but not business-specific upload deadlines. Establishing whether the date belongs to the event requires a separate plausibility check against the source. An incorrectly declared unit can still produce a technically valid but incorrect date.

For example, interpreting 1790465400 as milliseconds produces 1970-01-21T17:21:05.400Z. Interpreting milliseconds as seconds can produce a distant future date or a range error. Even an obviously incorrect date is not harmless once an API or report processes the value.

3. GA4 reporting date and timestamp have different meanings

The current GA4 export schema describes event_timestamp as receipt time at Google Analytics in UTC microseconds. Multiple events in the same request can share a timestamp. The value is therefore neither a unique event key nor, without further checks, a precise click time.

event_date is the logged date in YYYYMMDD notation under the configured reporting time zone. Daily tables use the name events_YYYYMMDD. A local date calculated from the receipt timestamp must not automatically be equated with the stored event date: delays, timestamp semantics and changes to the reporting time zone require separate consideration.

For a pure time-zone comparison of the same example instant:

UTC                2026-09-26T23:30:00Z
Europe/Berlin      2026-09-27T01:30:00+02:00
UTC date           2026-09-26
Berlin date        2026-09-27

Berlin is two hours ahead of UTC in September and normally one hour ahead in winter. Local-day and UTC-day reports therefore select different intervals. The difference between daily totals is not constant: events move across both boundaries, and the counts depend on actual activity.

Comparisons need consistent day definitions, event meanings, filters and data freshness. A table filter selects export tables; an additional timestamp filter selects instants within those tables. Reconstructing a different reporting day also requires considering boundary tables and late data. A LIMIT only limits output and does not reliably limit bytes read.

4. BigQuery functions for each unit

TIMESTAMP_SECONDS, TIMESTAMP_MILLIS and TIMESTAMP_MICROS require the unit indicated by each name. This example selects a few fields from one explicitly named daily table. Project, dataset, date and reporting time zone require adjustment.

SELECT
  event_date,
  event_timestamp,
  TIMESTAMP_MICROS(event_timestamp) AS timestamp_utc,
  DATETIME(TIMESTAMP_MICROS(event_timestamp),
           'Europe/Berlin') AS wall_clock_berlin,
  DATE(TIMESTAMP_MICROS(event_timestamp)) AS date_utc,
  DATE(TIMESTAMP_MICROS(event_timestamp),
       'Europe/Berlin') AS timestamp_date_berlin,
  FORMAT_TIMESTAMP('%Y-%m-%dT%H:%M:%E6S%Ez',
                   TIMESTAMP_MICROS(event_timestamp),
                   'Europe/Berlin') AS text_with_offset,
  UNIX_SECONDS(TIMESTAMP_MICROS(event_timestamp)) AS whole_seconds
FROM `project_id.analytics_123456789.events_20260927`
LIMIT 10;

TIMESTAMP represents an instant. DATETIME contains a calendar date and clock reading without a time zone. A local display is useful, but a unique reverse conversion requires additional information. %E6S preserves six fractional digits in the output string; UNIX_SECONDS rounds down to the beginning of the second and discards microseconds.

Parsing text with an explicit offset, an explicit zone or the UTC default produces different results:

SELECT
  PARSE_TIMESTAMP('%FT%T%Ez', '2026-09-27T01:30:00+02:00') AS explicit_offset,
  PARSE_TIMESTAMP('%FT%T', '2026-09-27T01:30:00',
                  'Europe/Berlin') AS explicit_zone,
  PARSE_TIMESTAMP('%FT%T', '2026-09-27T01:30:00') AS utc_default;

The first two results identify 2026-09-26T23:30:00Z; the third identifies 2026-09-27T01:30:00Z, two hours later. A format string alone does not establish the correct time zone. Other supported parsing functions also need clear rules for the unit, format and time zone of the input.

5. Daylight saving creates repeated and missing clock times

In Europe/Berlin, 02:30 occurs twice on 25 October 2026: with +02:00, the value means 00:30 UTC; with +01:00, the value means 01:30 UTC. The zone name alone does not resolve this ambiguity. A known offset or an explicit choice of the first or second occurrence is required.

Conversely, 02:30 does not exist in Berlin on 29 March 2026 because the clock jumps from 02:00 to 03:00. A parser can shift, reject or otherwise resolve such input according to its rules. A silent default does not reconstruct the actual event.

Those calendar days last 25 and 23 hours respectively. Day boundaries therefore come from local midnight and the next local midnight, converted separately to UTC. Adding 86,400 seconds is not a general substitute.

For events that have already occurred, a uniquely established UTC instant is useful for storage. The original zone and source text can also be retained for traceability. UTC storage does not repair a local time that was interpreted incorrectly beforehand.

6. Rules of the receiving API

Meta CAPI uses Unix seconds of the actual event for event_time. An unmodified Date.now() value is therefore unsuitable. Permitted event age and error handling belong to the specific API and event source; a successful HTTP response alone does not establish complete processing or attribution. Error objects, response details and diagnostics need separate examination.

The Google Ads API requires a time-zone designation for ClickConversion.conversion_date_time, using yyyy-mm-dd hh:mm:ss+|-hh:mm. The matching example is 2026-09-27 01:30:00+02:00. A missing offset must not generally be interpreted as a fallback to the account time zone. Other upload routes have separate requirements.

Retries must retain the actual event time. Replacing an old event time with “now” to bypass a deadline falsifies the data. Following a verified clock correction, the original event time, allowed window and duplicate protection need to be checked together.

Sources and technical references

• RFC3339
• ECMAScript: Numbers and dates
• Python: datetime
• Python: zoneinfo
• GA4: BigQuery export schema
• BigQuery: Timestamp functions
• Google Ads: ClickConversion
• Meta: ServerEvent SDK
• GA4: Property time zone
• RFC9110: Date
• systemd: timedatectl
• GTM: Data layer