Filter by timestamp or offset

Understand using Kafka topics by timestamp or offset in Lenses SQL Snapshot concepts engine for Apache Kafka

A popular question is "how do I see the most recent messages?" or "how do i filter for a specific date"

Lenses query editor shows you the oldest events (the earliest) because this is how Kafka is designed.

To skip the old events and see the most recent very fast, you need to either filter on time or offset with these filters. A list of functions is available here SQL reference

View the most recent messages

Filter by timestamp

Use the now() function to view events from the last 5 minutes:

SELECT * FROM mytopic WHERE _meta.timestamp > now() - "5m" LIMIT 100;

Filter by offset

View the most recent 100 events :

SELECT * FROM mytopic WHERE _meta.offset >= LAST_OFFSET() - 10 LIMIT 100;

Slice from the past

Filter by timestamp

SELECT *
FROM position_reports
WHERE
   _meta.timestamp > "2020-04-01" AND
   _meta.timestamp < "2020-04-02";

Same example with time filter :

SELECT * from mytopic
WHERE 
    _meta.timestamp > '2024-10-16 14:30:00' AND 
    _meta.timestamp < "2025-04-02 14:30:00"
;

Filter by Offset

the same logic can be applied for filtering by offset :

SELECT * FROM mytopic 
WHERE 
    _meta.offset >= 100 and
    _meta.offset < 1000;