Working with Timestamps

Handling time-based data in SQL is essential for analyzing trends, patterns, and other time-related metrics. With Telemetry's flexibility to ingest any arbitrary JSON, you can leverage SQL's powerful time functions to extract meaningful insights from your data. This article provides an overview of how to use time functions in SQL queries, focusing on grouping, truncating, and analyzing events over time, particularly within the constraints of the DataFusion SQL engine.

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 Log Entry:

{
  "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.

Benefits of Using timestamp_utc:

Faster query execution, especially for large datasets.
Optimized storage due to the compact timestamp format.
Compatibility with DataFusion’s SQL engine for time-based operations like DATE_TRUNC, EXTRACT, and more.

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

You may often need to extract specific parts of a timestamp, such as the year, month, day, or even hour. SQL provides functions like EXTRACT() and DATE_TRUNC() to help you do this.

Example: Extracting 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 the day of the week (0-6) for each event, where 0 represents Sunday and 6 represents Saturday.

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.

Example: Grouping 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.

Example: Week-over-Week Comparison

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

In some SQL engines, such as PostgreSQL, you might use the AGE() function to calculate the difference between two timestamps. However, DataFusion does not support the AGE() function. Instead, you can achieve similar results using other methods.

Example: Calculating Time Differences Between Events

To calculate the time difference between two events in DataFusion, you can use the LAG() function along with direct arithmetic on the timestamps.

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

Time functions in SQL are indispensable tools for analyzing and managing time-based data. While DataFusion has certain limitations compared to other SQL engines, you can still effectively use its supported functions to group, truncate, and analyze your data over various time intervals. Whether you're tracking daily events, comparing weekly trends, or calculating time differences between events, SQL provides the necessary functions to unlock the full potential of your time-based data.

Leverage these powerful SQL time functions within Telemetry to gain deeper insights and make more informed decisions based on your data.

Turn timestamped events into a reusable operational view