Calculate Sessions and Elapsed Time

Calculate sessions and elapsed time by identifying when state changes occur in time-series data. This "flip-flop" or "session" pattern is useful for analyzing user sessions, vehicle rides, machine operating cycles, or any scenario where you need to track duration between state transitions.

Problem: Track Time Between State Changes​

You have a table tracking vehicle lock status over time and want to calculate ride duration. A ride starts when lock_status changes from true (locked) to false (unlocked), and ends when it changes back to true.

Table schema:

CREATE TABLE vehicle_events (
  vehicle_id SYMBOL,
  lock_status BOOLEAN,
  timestamp TIMESTAMP
) TIMESTAMP(timestamp) PARTITION BY DAY;

Sample data:

timestampvehicle_idlock_status
10:00:00V001true
10:05:00V001false
10:25:00V001true
10:30:00V001false
10:45:00V001true

You want to calculate the duration of each ride.

Solution: Session Detection with Window Functions​

Use window functions to detect state changes, assign session IDs, then calculate durations:

Calculate ride duration from lock status changes
WITH prevEvents AS (
  SELECT *,
    lag(lock_status::int) -- lag doesn't support booleans, so we convert to 1 or 0
      OVER (PARTITION BY vehicle_id ORDER BY timestamp) as prev_status
  FROM vehicle_events
  WHERE timestamp IN today()
),
ride_sessions AS (
  SELECT *,
    SUM(CASE
      WHEN lock_status = true AND prev_status = 0 THEN 1
      WHEN lock_status = false AND prev_status = 1 THEN 1
      ELSE 0
    END) OVER (PARTITION BY vehicle_id ORDER BY timestamp) as ride
  FROM prevEvents
),
global_sessions AS (
  SELECT *, concat(vehicle_id, '#', ride) as session
  FROM ride_sessions
),
totals AS (
  SELECT
    first(timestamp) as ts,
    session,
    FIRST(lock_status) as lock_status,
    first(vehicle_id) as vehicle_id
  FROM global_sessions
  GROUP BY session
),
prev_ts AS (
  SELECT *,
    lag(timestamp) OVER (PARTITION BY vehicle_id ORDER BY timestamp) as prev_ts
  FROM totals
)
SELECT
  timestamp as ride_end,
  vehicle_id,
  datediff('s', prev_ts, timestamp) as duration_seconds
FROM prev_ts
WHERE lock_status = false AND prev_ts IS NOT NULL;

Results:

ride_endvehicle_idduration_seconds
10:25:00V0011200
10:45:00V001900

How It Works​

The query uses a five-step approach:

1. Get Previous Status (prevEvents)​

lag(lock_status::int) OVER (PARTITION BY vehicle_id ORDER BY timestamp)

For each row, get the status from the previous row. Convert boolean to integer (0/1) since lag doesn't support boolean types directly.

2. Detect State Changes (ride_sessions)​

SUM(CASE WHEN lock_status != prev_status THEN 1 ELSE 0 END)
  OVER (PARTITION BY vehicle_id ORDER BY timestamp)

Whenever status changes, increment a counter. This creates sequential session IDs for each vehicle:

  • Ride 0: Initial state
  • Ride 1: After first state change
  • Ride 2: After second state change
  • ...

3. Create Global Session IDs (global_sessions)​

concat(vehicle_id, '#', ride)

Combine vehicle_id with ride number to create unique session identifiers across all vehicles.

4. Get Session Start Times (totals)​

SELECT first(timestamp) as ts, ...
FROM global_sessions
GROUP BY session

For each session, get the timestamp and status at the beginning of that session.

5. Calculate Duration (prev_ts)​

lag(timestamp) OVER (PARTITION BY vehicle_id ORDER BY timestamp)

Get the timestamp from the previous session (for the same vehicle), then use datediff('s', prev_ts, timestamp) to calculate duration in seconds.

Filter for Rides​

WHERE lock_status = false

Only show sessions where status is false (unlocked), which represents completed rides. The duration is from the previous session end (lock) to this session start (unlock).

Monthly Aggregation​

Calculate total ride duration per vehicle per month:

Monthly ride duration by vehicle
WITH prevEvents AS (
  SELECT *,
    lag(lock_status::int) -- lag doesn't support booleans, so we convert to 1 or 0
      OVER (PARTITION BY vehicle_id ORDER BY timestamp) as prev_status
  FROM vehicle_events
  WHERE timestamp >= dateadd('M', -3, now())
),
ride_sessions AS (
  SELECT *,
    SUM(CASE
      WHEN lock_status = true AND prev_status = 0 THEN 1
      WHEN lock_status = false AND prev_status = 1 THEN 1
      ELSE 0
    END) OVER (PARTITION BY vehicle_id ORDER BY timestamp) as ride
  FROM prevEvents
),
global_sessions AS (
  SELECT *, concat(vehicle_id, '#', ride) as session
  FROM ride_sessions
),
totals AS (
  SELECT
    first(timestamp) as ts,
    session,
    FIRST(lock_status) as lock_status,
    first(vehicle_id) as vehicle_id
  FROM global_sessions
  GROUP BY session
),
prev_ts AS (
  SELECT *,
    lag(timestamp) OVER (PARTITION BY vehicle_id ORDER BY timestamp) as prev_ts
  FROM totals
)
SELECT
  timestamp_floor('M', timestamp) as month,
  vehicle_id,
  SUM(datediff('s', prev_ts, timestamp)) as total_ride_duration_seconds,
  COUNT(*) as ride_count
FROM prev_ts
WHERE lock_status = false AND prev_ts IS NOT NULL
GROUP BY month, vehicle_id
ORDER BY month, vehicle_id;

Adapting to Different Use Cases​

User website sessions (1 hour timeout):

WITH prevEvents AS (
  SELECT *,
    lag(timestamp) OVER (PARTITION BY user_id ORDER BY timestamp) as prev_ts
  FROM page_views
),
sessions AS (
  SELECT *,
    SUM(CASE
      WHEN datediff('h', prev_ts, timestamp) > 1 THEN 1
      ELSE 0
    END) OVER (PARTITION BY user_id ORDER BY timestamp) as session_id
  FROM prevEvents
)
SELECT
  user_id,
  session_id,
  min(timestamp) as session_start,
  max(timestamp) as session_end,
  datediff('s', min(timestamp), max(timestamp)) as session_duration_seconds,
  count(*) as page_views
FROM sessions
GROUP BY user_id, session_id;

Machine operating cycles:

-- When machine changes from 'off' to 'running' to 'off'
WITH prevStatus AS (
  SELECT *,
    lag(status) OVER (PARTITION BY machine_id ORDER BY timestamp) as prev_status
  FROM machine_status
),
cycles AS (
  SELECT *,
    SUM(CASE
      WHEN status != prev_status THEN 1
      ELSE 0
    END) OVER (PARTITION BY machine_id ORDER BY timestamp) as cycle
  FROM prevStatus
)
SELECT
  machine_id,
  cycle,
  min(timestamp) as cycle_start,
  max(timestamp) as cycle_end
FROM cycles
WHERE status = 'running'
GROUP BY machine_id, cycle;
Common Session Patterns

This pattern applies to many scenarios:

  • User sessions: Time between last action and timeout
  • IoT device cycles: Power on/off cycles
  • Vehicle trips: Ignition on/off periods
  • Connection sessions: Login/logout tracking
  • Process steps: Start/complete state transitions
First Row Handling

The first row in each partition will have NULL for previous values. Always filter these out with WHERE prev_ts IS NOT NULL or similar conditions.

Related Documentation