SQL data types

Telemetry infers stored event fields from ingested data. SQL expressions can produce additional types, and casts convert values at query time. A cast does not change the stored event schema.

Inspect and convert values

SELECT
  arrow_typeof(timestamp_utc) AS time_type,
  arrow_typeof(duration_ms) AS duration_type,
  CAST(duration_ms AS DOUBLE) AS duration_number,
  TRY_CAST(status_code AS BIGINT) AS parsed_status
FROM product_events
LIMIT 10;

CAST(value AS type) and value::type request a conversion. Invalid values can cause CAST to fail the query. TRY_CAST(value AS type) returns NULL for failed supported conversions; an unsupported conversion or unknown target type can still fail planning. Neither form guarantees lossless conversion: narrowing integers, reducing timestamp precision, and converting floating-point numbers require care.

arrow_typeof(expression) reports the actual execution type. arrow_cast(expression, 'Arrow type') supports precise Arrow type names; ordinary SQL casts are clearer for common conversions.

Scalar type names

Decimals support up to 76 digits of precision; precisions through 38 use the 128-bit decimal representation and larger precisions use 256-bit decimals. Choose an explicit precision and scale when decimal arithmetic matters.

String expressions can use Arrow Utf8, LargeUtf8, or Utf8View representations. These are implementation representations of text, not different quoting rules. Use VARCHAR without a length for portable casts in Telemetry.

Do not assume UUID, JSON, JSONB, DATETIME, BLOB, or TIME WITH TIME ZONE are supported cast targets. Keep UUIDs as strings and use native structured fields for nested events.

Event time and timezones

Telemetry's managed timestamp_utc column is Timestamp(Millisecond, Some("UTC")). The query session leaves its optional timezone configuration unset: a TIMESTAMPTZ cast therefore does not automatically attach UTC metadata. Use the native column for event-time calculations, and do not rely on that cast to establish a timezone. Prefer timezone-qualified input when supplying event time. Filter with explicit conversion of timestamp strings or epoch values:

SELECT timestamp_utc, event_id
FROM product_events
WHERE timestamp_utc >= to_timestamp('2026-09-01T00:00:00Z')
  AND timestamp_utc < to_timestamp('2026-10-01T00:00:00Z')
ORDER BY timestamp_utc;

Use half-open intervals (>= start AND < end) for adjacent reporting periods. They avoid double-counting the boundary.

For numeric epoch input, select the function matching its units: to_timestamp_seconds, to_timestamp_millis, to_timestamp_micros, or to_timestamp_nanos. Never guess whether a numeric value represents seconds or milliseconds. A plain TIMESTAMP cast has no timezone metadata; preserve or explicitly establish timezone semantics when comparing data from multiple sources.

SELECT
  to_timestamp_millis(1704067200000) AS event_time,
  now() - INTERVAL '7 days' AS seven_days_ago,
  date_trunc('day', now()) AS day_start;

Calendar intervals such as INTERVAL '1 month' differ from fixed elapsed durations such as INTERVAL '30 days'. Timestamp subtraction returns an interval; date_part('epoch', end_time - start_time) expresses the result in seconds. See the date and time functions.

AT TIME ZONE

expression AT TIME ZONE 'zone' casts to a nanosecond timestamp carrying the specified timezone. The zone must be a string literal, such as 'UTC' or 'America/New_York'; a timezone column is not supported in this syntax.

An offset-qualified string or an already timezone-aware timestamp preserves the instant and displays it in the target zone. A string or timestamp without a timezone is interpreted as wall-clock time in the target zone. Removing timezone information before applying this operation can therefore change the instant:

SELECT
  '2024-03-30T00:00:20Z' AT TIME ZONE 'Europe/Brussels' AS instant_in_brussels,
  TIMESTAMP '2024-03-30 00:00:20' AT TIME ZONE 'Europe/Brussels' AS brussels_wall_time;

These display as 2024-03-30T01:00:20+01:00 and 2024-03-30T00:00:20+01:00, respectively. The result retains timezone metadata even when the input was timezone-aware. To obtain a timezone-free local wall-clock value, use to_local_time; for local calendar grouping, use local_time_bucket.

Arrays and structs

Arrays contain elements with a compatible type. SQL supports typed array casts such as BIGINT[] and ARRAY<BIGINT>, plus fixed-size forms such as BIGINT[3]. Bare ARRAY without an element type is not a complete cast target.

SELECT
  CAST([1, 2, 3] AS BIGINT[]) AS values,
  named_struct('name', 'api', 'healthy', true) AS service;

Structs contain named, typed fields. Nested objects in events are exposed as structs; read them with a field path or bracket access. SQL supports struct type definitions such as STRUCT<name VARCHAR, healthy BOOLEAN> where a typed expression is needed. Array indexes in SQL are one-based. See nested data functions for construction, lookup, transformation, and expansion.

Nulls and missing fields

NULL represents an absent or unknown value. It is not zero, false, or the empty string. Newly introduced fields can be null on older rows. Fields absent from the complete table schema can produce an unknown-column error rather than a null value.

Most arithmetic and comparison expressions propagate null. NULL = NULL is unknown; use IS NULL, IS NOT NULL, or null-safe comparison. WHERE retains only rows whose predicate is true, excluding both false and unknown.

SELECT
  COUNT(*) AS rows,
  COUNT(duration_ms) AS rows_with_duration,
  AVG(duration_ms) AS mean_reported_duration,
  SUM(CASE WHEN duration_ms IS NULL THEN 1 ELSE 0 END) AS missing_duration
FROM product_events;

COUNT(*) counts rows. COUNT(expression) counts non-null values. Aggregates such as SUM and AVG ignore null inputs; on an empty input, COUNT returns zero while many other aggregates return null. COALESCE(duration_ms, 0) changes the analytical meaning to treating missing durations as measured zero.