Managing time in SSB
Time attributes define how streams are processed in time. There are two high-level options for providing time attributes to your SQL Stream Builder queries.
SELECT flight_number, flight_origin, flight_destination, TUMBLE_END("eventTimestamp", INTERVAL '5' MINUTE) AS window_end_timestamp FROM airplane_flights GROUP BY TUMBLE("eventTimestamp", INTERVAL '5' MINUTE)
SELECT a.web_order_id, a.product_name, a.order_date b.next_shipment_time FROM online_orders a, shipment_events b WHERE a.shipping_type = 'Priority' AND a.eventTimestamp BETWEEN b.eventTimestamp - INTERVAL '1' HOUR AND b.eventTimestamp
The user can also specify timestamps contained in the data stream itself.
- The timestamp should be contained in a column of type "long".
- The timestamp should be in epoch format (eg. milliseconds since Jan 1, 1970).
SELECT * FROM airplane_flights a WHERE flight_number IS NOT NULL GROUP BY HOP(a."timestamp_ms", INTERVAL '15' SECOND, INTERVAL '5' SECOND), flight_number
If an invalid timestamp is encountered in the stream (eg. NaN), then that message's timestamp defaults to 0, causing the message to be excluded from the current window.
If your data does not include a timestamp in a suitable format, it's possible to compute a new timestamp column from another existing column (using input transformations).