Trabajar con marcas de tiempo

Cuente eventos por día, compare semanas o mida el intervalo entre eventos con el motor DataFusion SQL de Telemetry. Los ejemplos siguientes muestran cada cálculo.

Almacenar marcas de tiempo consistentemente

Telemetry garantiza que cada evento tenga una marca de tiempo. Si la carga útil omite timestamp, el registro API agrega la hora UTC actual. También puede proporcionar un valor ISO 8601 calificado para zona horaria o una marca de tiempo Unix cuando la hora del evento de origen difiere del tiempo de ingesta. Telemetry expone la columna timestamp_utc optimizada para operaciones SQL.

Ejemplo de entrada de registro:

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

En este registro, la fuente proporciona su propia hora del evento. Cuando se omite el campo, se utiliza el tiempo de ingesta.

Consulta de datos basados en el tiempo

Utilice timestamp_utc para filtrar, agrupar, clasificar y aritmética del tiempo. Tiene un tipo de marca de tiempo UTC nativa y es la columna de consulta coherente en todas las tablas Telemetry.

Beneficios de usar timestamp_utc:

• Ejecución de consultas más rápida, especialmente para grandes conjuntos de datos.
• Almacenamiento optimizado gracias al formato compacto de marca de tiempo.
• Compatibilidad con el motor SQL de DataFusion para operaciones basadas en tiempo como DATE_TRUNC, EXTRACT y más.

No es necesario enviar timestamp para los eventos creados en el momento de la ingesta. Si lo envía, utilice un valor calificado para zona horaria para que la hora local no sea ambigua.

Consulta con funciones de tiempo.

1. Extraer partes de fecha

Use EXTRACT() para obtener una parte de la marca de tiempo, como el año o la hora. Use DATE_TRUNC() para redondearla hacia abajo al inicio de un intervalo.

Ejemplo: extracción del día de la semana

Para extraer el día de la semana de la marca de tiempo, puedes usar:

SELECT 
    EXTRACT(DOW FROM timestamp_utc) AS day_of_week
FROM 
    event_log

Esta consulta devuelve el día de la semana (0-6) para cada evento, donde 0 representa el domingo y 6 representa el sábado.

2. Agrupar por intervalo de tiempo

Un caso de uso común de las funciones de tiempo es agrupar eventos por intervalos de tiempo específicos, como días, semanas o meses, para ver cuántos eventos ocurrieron durante cada período.

Ejemplo: agrupar eventos por día

Para agrupar eventos por día y contar el número de eventos por día, puede usar 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

Esta consulta trunca la marca de tiempo al nivel de día y luego agrupa los eventos por estos días truncados. El resultado es un recuento de los eventos que ocurrieron cada día.

3. Compara rangos de tiempo

Es posible que también desee comparar datos de diferentes rangos de tiempo, como comparar eventos de una semana a la siguiente.

Ejemplo: Comparación semana tras semana

Para comparar la cantidad de eventos entre diferentes semanas, puede usar:

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;

Esta consulta agrupa eventos por semana en que ocurrieron, lo que le permite analizar tendencias semanales.

4. Calcular las diferencias horarias

PostgreSQL admite AGE() para calcular diferencias entre marcas de tiempo. DataFusion no admite AGE(), así que reste las marcas de tiempo directamente.

Ejemplo: cálculo de diferencias horarias entre eventos

Use LAG() para obtener la hora del evento anterior y réstela de la hora del evento actual.

Por ejemplo, para calcular la diferencia en segundos entre el evento actual y el evento anterior:

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 recupera la hora del evento anterior y date_part('epoch', ...) convierte el intervalo de marca de tiempo en segundos totales. La primera fila no tiene marca de tiempo anterior, por lo que su diferencia es nula.

Próximos pasos

Las funciones de tiempo en SQL son herramientas indispensables para analizar y administrar datos basados en el tiempo. Si bien DataFusion tiene ciertas limitaciones en comparación con otros motores SQL, aún puede utilizar de manera efectiva sus funciones admitidas para agrupar, truncar y analizar sus datos en varios intervalos de tiempo. Ya sea que esté rastreando eventos diarios, comparando tendencias semanales o calculando diferencias horarias entre eventos, SQL proporciona las funciones necesarias para desbloquear todo el potencial de sus datos basados ​​en el tiempo.

Aproveche estas potentes funciones de tiempo SQL dentro de Telemetry para obtener conocimientos más profundos y tomar decisiones más informadas basadas en sus datos.