Internal site. Jolli authentication required to view.
Skip to Content
📋 SQL ReferenceTime Series Sql

Last Updated: 3/19/2026

Time-series and Streaming SQL

Feldera provides specialized SQL features for time-series data and streaming queries, including LATENESS declarations, watermarks, and time-based aggregations.

LATENESS Declarations

Declare LATENESS on timestamp columns to bound out-of-order data:

CREATE TABLE events (
    event_id BIGINT,
    event_time TIMESTAMP LATENESS INTERVAL 1 HOUR,
    user_id BIGINT,
    action VARCHAR
);

The LATENESS attribute specifies the maximum amount by which event times can arrive out of order. Events more than 1 hour late are considered late and handled specially.

Watermarks

LATENESS creates implicit watermarks that track progress through time:

CREATE TABLE sensor_data (
    sensor_id INTEGER,
    reading_time TIMESTAMP LATENESS INTERVAL 5 MINUTE,
    temperature DOUBLE
);

The watermark advances as data arrives, ensuring time-based operations produce correct results even with out-of-order data.

Time-based Windows

Tumbling Windows

Fixed-size, non-overlapping windows:

CREATE VIEW hourly_stats AS
SELECT 
    DATE_TRUNC('hour', event_time) as window_start,
    COUNT(*) as event_count,
    AVG(value) as avg_value
FROM events
GROUP BY DATE_TRUNC('hour', event_time);

Sliding Windows

Overlapping windows using window functions:

CREATE VIEW moving_average AS
SELECT 
    event_time,
    value,
    AVG(value) OVER (
        ORDER BY event_time
        ROWS BETWEEN 10 PRECEDING AND CURRENT ROW
    ) as moving_avg
FROM events;

Time-based Joins

Join streams with time bounds:

CREATE VIEW correlated_events AS
SELECT 
    e1.event_id as event1_id,
    e2.event_id as event2_id,
    e1.event_time,
    e2.event_time
FROM events e1
JOIN events e2 
    ON e1.user_id = e2.user_id
    AND e2.event_time BETWEEN e1.event_time AND e1.event_time + INTERVAL 1 HOUR;

NOW() Function

Use NOW() for real-time queries:

CREATE VIEW recent_events AS
SELECT * FROM events
WHERE event_time > NOW() - INTERVAL 1 HOUR;

Configure clock resolution in runtime config:

{
  "clock_resolution_usecs": 1000000
}

Time-series Aggregations

Running Totals

CREATE VIEW running_totals AS
SELECT 
    event_time,
    user_id,
    amount,
    SUM(amount) OVER (
        PARTITION BY user_id
        ORDER BY event_time
    ) as running_total
FROM transactions;

Time-based Grouping

CREATE VIEW daily_metrics AS
SELECT 
    DATE_TRUNC('day', event_time) as day,
    metric_name,
    COUNT(*) as count,
    AVG(value) as avg_value,
    MIN(value) as min_value,
    MAX(value) as max_value
FROM metrics
GROUP BY day, metric_name;

Handling Late Data

Late data (beyond LATENESS) triggers the late() function:

-- Late events are counted separately
CREATE VIEW event_stats AS
SELECT 
    COUNT(*) as total_events,
    SUM(CASE WHEN is_late THEN 1 ELSE 0 END) as late_events
FROM events;

Monitor late records via metrics:

stats = pipeline.stats()
# Check for late records in logs

Session Windows

Group events into sessions based on inactivity:

CREATE VIEW user_sessions AS
SELECT 
    user_id,
    MIN(event_time) as session_start,
    MAX(event_time) as session_end,
    COUNT(*) as event_count
FROM events
GROUP BY user_id, session_id;

What’s Next

Sql Udfs