Working with timestamps

Count events by day, compare weeks, or measure the gap between events with Telemetry's DataFusion SQL engine. The examples below cover each calculation.

Store timestamps consistently

Telemetry ensures every event has a timestamp. If the payload omits timestamp, the Log API adds the current UTC time. You can also provide a timezone-qualified ISO 8601 value or Unix timestamp when the source event time differs from ingestion time. Telemetry exposes the optimized timestamp_utc column for SQL operations.

Example event:

{
  "event_id": "e12345",
  "event_type": "click",
  "user_id": "u67890",
  "timestamp": "2026-07-27T14:22:00.000Z",
  "metadata": {
    "browser": "Chrome",
    "device": "Mobile",
    "location": "New York"
  }
}

In this log, the source provides its own event time. When the field is omitted, ingestion time is used instead.

Querying time-based data

Use timestamp_utc for filtering, bucketing, sorting, and time arithmetic. It has a native UTC timestamp type and is the consistent query column across Telemetry tables.

Use timestamp_utc for:

• Querying large datasets with a native timestamp column.
• Storing timestamps in a compact format.
• Time calculations in DataFusion with DATE_TRUNC and EXTRACT.

You do not need to send timestamp for events created at ingestion time. If you do send it, use a timezone-qualified value so local time is not ambiguous.

Query with time functions

1. Extract date parts

Use EXTRACT() to read a timestamp part, such as the year or hour. Use DATE_TRUNC() to round a timestamp down to an interval boundary.

Extract the day of the week

To extract the day of the week from the timestamp, you can use:

SELECT 
    EXTRACT(DOW FROM timestamp_utc) AS day_of_week
FROM 
    event_log

This query returns a number from 0 to 6 for each event. Sunday is 0 and Saturday is 6.

2. Group by time interval

One common use case for time functions is to group events by specific time intervals, such as days, weeks, or months, to see how many events occurred during each period.

Count events by day

To group events by day and count the number of events per day, you can use DATE_TRUNC():

SELECT 
    DATE_TRUNC('day', timestamp_utc) AS day,
    COUNT(*) AS event_count 
FROM 
    event_log 
GROUP BY 
    DATE_TRUNC('day', timestamp_utc) 
ORDER BY 
    day

This query truncates the timestamp to the day level and then groups the events by these truncated days. The result is a count of events that occurred on each day.

3. Compare time ranges

You may also want to compare data from different time ranges, such as comparing events from one week to the next.

Compare weeks

To compare the number of events between different weeks, you can use:

SELECT 
    DATE_TRUNC('week', timestamp_utc) AS week,
    COUNT(*) AS event_count 
FROM 
    event_log 
GROUP BY 
    DATE_TRUNC('week', timestamp_utc) 
ORDER BY 
    week;

This query groups events by the week they occurred, allowing you to analyze weekly trends.

4. Calculate time differences

PostgreSQL supports AGE() for timestamp differences. DataFusion does not support AGE(), so subtract timestamps directly.

Calculate the gap between events

Use LAG() to get the previous event time, then subtract it from the current event time.

For instance, to calculate the difference in seconds between the current event and the previous event:

WITH ordered_events AS (
  SELECT
    event_id,
    timestamp_utc,
    LAG(timestamp_utc) OVER (ORDER BY timestamp_utc) AS previous_timestamp
  FROM event_log
)
SELECT
  event_id,
  timestamp_utc,
  previous_timestamp,
  date_part('epoch', timestamp_utc - previous_timestamp)
    AS seconds_since_previous_event
FROM ordered_events
ORDER BY timestamp_utc;

LAG retrieves the prior event time, and date_part('epoch', ...) converts the timestamp interval to total seconds. The first row has no previous timestamp, so its difference is null.

Next steps

Choose a time interval that matches your question. Use daily buckets to compare event volumes, weekly buckets to compare longer trends, and timestamp differences to measure elapsed time.

Check the timezone before comparing counts from different systems.