How We Track Visit Duration — The Technical Breakdown

Technical companion to Visit Duration in The Numbers That Matter series.

---

Visit duration is the time between check-in and checkout. It's the simplest metric to calculate but one of the hardest to collect accurately — because it requires a real checkout event, not just a member walking out the door.

The base calculation

SELECT
  v.id,
  v.memberId,
  v.checkInAt,
  v.checkOutAt,
  EXTRACT(EPOCH FROM (v.checkOutAt - v.checkInAt)) / 60 AS duration_minutes,
  EXTRACT(EPOCH FROM (v.checkOutAt - v.checkInAt)) / 3600 AS duration_hours
FROM visits v
WHERE v.checkOutAt IS NOT NULL
  AND v.checkInAt BETWEEN :start AND :end;

The checkOutAt IS NOT NULL filter is important. Visits without a checkout (system timeout, abandoned NFC bracelet, edge cases) should be excluded from duration analytics or flagged separately — they'd skew your averages.

Distribution analysis

The operator post mentions that duration often clusters rather than forming a smooth curve. To find the clusters:

-- Duration histogram in 30-minute buckets
SELECT
  FLOOR(EXTRACT(EPOCH FROM (checkOutAt - checkInAt)) / 1800) * 30 AS bucket_minutes,
  COUNT(*) AS visit_count,
  ROUND(COUNT(*)::numeric / SUM(COUNT(*)) OVER () * 100, 1) AS pct_of_total
FROM visits
WHERE checkOutAt IS NOT NULL
  AND checkInAt BETWEEN :start AND :end
GROUP BY bucket_minutes
ORDER BY bucket_minutes;

This produces a histogram. Spikes at certain durations reveal your natural customer segments — the quick-visit crowd, the half-day settlers, and anyone in between.

By room type

Different room types naturally produce different durations:

SELECT
  r.roomType,
  COUNT(*) AS visits,
  ROUND(AVG(EXTRACT(EPOCH FROM (v.checkOutAt - v.checkInAt)) / 60), 1) AS avg_duration_min,
  ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (
    ORDER BY EXTRACT(EPOCH FROM (v.checkOutAt - v.checkInAt))
  ) / 60, 1) AS median_duration_min,
  ROUND(STDDEV(EXTRACT(EPOCH FROM (v.checkOutAt - v.checkInAt)) / 60), 1) AS stddev_min
FROM visits v
JOIN rentals rental ON rental.visitId = v.id
JOIN rooms r ON r.id = rental.roomId
WHERE v.checkOutAt IS NOT NULL
  AND v.checkInAt BETWEEN :start AND :end
GROUP BY r.roomType
ORDER BY avg_duration_min DESC;

The standard deviation is as informative as the average. A room type with a 120-minute average and 15-minute stddev has predictable turnover. One with the same average but 60-minute stddev is much harder to schedule around.

By day and time

Duration patterns shift across the week:

SELECT
  EXTRACT(DOW FROM v.checkInAt) AS day_of_week,
  CASE
    WHEN EXTRACT(HOUR FROM v.checkInAt) < 12 THEN 'morning'
    WHEN EXTRACT(HOUR FROM v.checkInAt) < 17 THEN 'afternoon'
    ELSE 'evening'
  END AS time_block,
  COUNT(*) AS visits,
  ROUND(AVG(EXTRACT(EPOCH FROM (v.checkOutAt - v.checkInAt)) / 60), 1) AS avg_duration_min
FROM visits v
WHERE v.checkOutAt IS NOT NULL
  AND v.checkInAt BETWEEN :start AND :end
GROUP BY day_of_week, time_block
ORDER BY day_of_week, time_block;

If weekday afternoon visits average 3 hours and Saturday evening visits average 90 minutes, your two audiences are behaving very differently — and might need different pricing structures.

Duration vs. revenue correlation

To understand whether longer visits generate more revenue (and by how much):

SELECT
  CASE
    WHEN duration_hours < 1 THEN 'under_1hr'
    WHEN duration_hours < 2 THEN '1-2hr'
    WHEN duration_hours < 3 THEN '2-3hr'
    WHEN duration_hours < 4 THEN '3-4hr'
    ELSE '4hr_plus'
  END AS duration_bucket,
  COUNT(*) AS visits,
  ROUND(AVG(total_revenue), 2) AS avg_rpv,
  ROUND(AVG(addon_revenue), 2) AS avg_addon_rpv,
  ROUND(AVG(addon_count), 1) AS avg_addons_per_visit
FROM (
  SELECT
    v.id,
    EXTRACT(EPOCH FROM (v.checkOutAt - v.checkInAt)) / 3600 AS duration_hours,
    COALESCE(SUM(t.amount), 0) AS total_revenue,
    COALESCE(SUM(t.amount) FILTER (WHERE t.type = 'addon'), 0) AS addon_revenue,
    COUNT(t.id) FILTER (WHERE t.type = 'addon') AS addon_count
  FROM visits v
  LEFT JOIN transactions t ON t.visitId = v.id
  WHERE v.checkOutAt IS NOT NULL
    AND v.checkInAt BETWEEN :start AND :end
  GROUP BY v.id, v.checkOutAt, v.checkInAt
) visit_data
GROUP BY duration_bucket
ORDER BY MIN(duration_hours);

This quantifies the revenue case for longer visits. If 4-hour visits generate 3x the add-on revenue of 1-hour visits, there's a clear economic argument for amenities and services that encourage longer stays.

Trending over time

Detecting experience quality changes through duration shifts:

SELECT
  DATE_TRUNC('week', checkInAt) AS week,
  ROUND(AVG(EXTRACT(EPOCH FROM (checkOutAt - checkInAt)) / 60), 1) AS avg_duration_min,
  COUNT(*) AS visits
FROM visits
WHERE checkOutAt IS NOT NULL
  AND checkInAt >= NOW() - INTERVAL '6 months'
GROUP BY DATE_TRUNC('week', checkInAt)
ORDER BY week;

A sustained downward trend in average duration — controlling for seasonal patterns — is a quality signal. Something about the experience is causing people to leave sooner, and it might not show up in any other metric until it's too late.

Capacity modeling

Duration combined with arrival patterns lets you model concurrent occupancy:

-- Estimated concurrent visitors by hour
WITH hourly_presence AS (
  SELECT
    generate_series(
      DATE_TRUNC('hour', v.checkInAt),
      DATE_TRUNC('hour', v.checkOutAt),
      INTERVAL '1 hour'
    ) AS hour_slot,
    v.id
  FROM visits v
  WHERE v.checkOutAt IS NOT NULL
    AND v.checkInAt BETWEEN :start AND :end
)
SELECT
  EXTRACT(HOUR FROM hour_slot) AS hour,
  ROUND(AVG(visitor_count), 1) AS avg_concurrent_visitors
FROM (
  SELECT
    hour_slot,
    COUNT(*) AS visitor_count
  FROM hourly_presence
  GROUP BY hour_slot
) hourly_counts
GROUP BY EXTRACT(HOUR FROM hour_slot)
ORDER BY hour;

This is the capacity curve the operator post describes — when your building is at its fullest, driven by the combination of arrival times and how long people stay. It's the data behind staffing schedules, fire code compliance, and expansion planning.

---

Back to the operator post: Visit Duration — what to do with this metric →