Analyze User Activity Patterns with MySQL and PHP

Knowing when people tend to use a service can help schedule moderation, support, notifications, maintenance, or capacity. A login table looks like an obvious source for that analysis, but it answers a narrower question than its name suggests: login timestamps reveal when sessions start, not how long somebody remains online.

That distinction matters. A person who signs in once and stays for four hours produces one event; another who reconnects ten times in an hour produces ten. The method below therefore measures login activity. Measuring online duration requires sessions with both a start and an end time.

Store Events in UTC

Use UTC for storage and convert only while grouping or presenting data. A composite index lets MySQL select one user's events over a bounded period without scanning the entire table.

CREATE TABLE login_event (
  login_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  logged_in_at DATETIME(6) NOT NULL,
  KEY ix_login_event_user_time (user_id, logged_in_at)
) ENGINE=InnoDB;

The application must write UTC values consistently. The analysis also needs a named IANA time zone such as Europe/Berlin, not a fixed offset such as +01:00. Named zones account for daylight-saving changes. MySQL's time-zone tables must be populated for CONVERT_TZ() to resolve those names.

Build a Complete 24-Hour Histogram

A grouped query normally omits hours with no events. That omission broke an important assumption in my original approach: the result no longer had one entry for every position on the 24-hour clock. A recursive common table expression supplies all 24 buckets and a left join fills absent hours with zero.

WITH RECURSIVE hours AS (
  SELECT 0 AS hour_of_day
  UNION ALL
  SELECT hour_of_day + 1
  FROM hours
  WHERE hour_of_day < 23
),
hourly_counts AS (
  SELECT
    HOUR(CONVERT_TZ(logged_in_at, 'UTC', ?)) AS hour_of_day,
    COUNT(*) AS event_count
  FROM login_event
  WHERE user_id = ?
    AND logged_in_at >= ?
    AND logged_in_at < ?
  GROUP BY HOUR(CONVERT_TZ(logged_in_at, 'UTC', ?))
),
histogram AS (
  SELECT
    hours.hour_of_day,
    COALESCE(hourly_counts.event_count, 0) AS event_count
  FROM hours
  LEFT JOIN hourly_counts USING (hour_of_day)
)
SELECT
  hour_of_day,
  event_count,
  event_count / NULLIF(SUM(event_count) OVER (), 0) AS activity_share
FROM histogram
ORDER BY hour_of_day;

Bind the same target time zone to the first and fifth placeholders. The date boundaries remain UTC values, so the composite index can constrain the scan before CONVERT_TZ() is evaluated. Use a meaningful observation period, such as the previous 90 complete days, and exclude the current partial day.

activity_share is the fraction of selected events in each hour. Unlike the geometric-mean threshold in my old solution, it has a direct interpretation and does not collapse when a bucket is zero. With no events, NULLIF() returns NULL instead of dividing by zero.

Find the Peak and Check the Sample

The peak hour is simply the bucket with the largest count. Always retain the event count alongside the result: claiming a behavioral pattern from three logins is not useful.

To request only the peak, replace the final SELECT of the histogram query with:

SELECT
  hour_of_day,
  event_count,
  event_count / NULLIF(SUM(event_count) OVER (), 0) AS activity_share
FROM histogram
ORDER BY event_count DESC, hour_of_day
LIMIT 1;

In application code, require a minimum sample chosen for the decision being made. A dashboard might display a provisional histogram after 30 events, while notification scheduling may need hundreds of events and several weeks of history. The threshold is a product decision, not a statistical constant.

Find a Continuous Active Window

Sometimes a single peak is less useful than a continuous window. First classify each hourly share with an explicit threshold. For example, a threshold of 1 / 24 selects hours whose share is at least the uniform baseline. That rule is easy to explain, though a percentile or a capacity-driven threshold may fit the application better.

The clock is circular, so a window from 22:00 through 02:00 must not be split at midnight. Duplicating the 24 values conceptually and limiting a run to 24 positions handles that boundary in linear time:

<?php

function longestCircularActivityWindow(array $activeHours): ?array
{
    if (count($activeHours) !== 24) {
        throw new InvalidArgumentException('Expected exactly 24 hourly values.');
    }

    $bestStart = null;
    $bestLength = 0;
    $runStart = 0;
    $runLength = 0;

    for ($index = 0; $index < 48; $index++) {
        if ((bool) $activeHours[$index % 24]) {
            if ($runLength === 0) {
                $runStart = $index;
            }

            $runLength = min($runLength + 1, 24);

            if ($runStart < 24 && $runLength > $bestLength) {
                $bestStart = $runStart;
                $bestLength = $runLength;
            }
        } else {
            $runLength = 0;
        }
    }

    if ($bestStart === null) {
        return null;
    }

    return [
        'start' => $bestStart % 24,
        'end' => ($bestStart + $bestLength) % 24,
        'length' => $bestLength,
    ];
}

end is exclusive. A result of ['start' => 22, 'end' => 2, 'length' => 4] means 22:00 to 02:00. Returning the length avoids ambiguity when all 24 hours are active, because both start and end are then zero.

Separate Weekdays from Weekends

A single daily histogram can hide two different routines. For operational planning, a seven-by-24 heatmap is often more informative:

SELECT
  WEEKDAY(CONVERT_TZ(logged_in_at, 'UTC', ?)) AS weekday_index,
  HOUR(CONVERT_TZ(logged_in_at, 'UTC', ?)) AS hour_of_day,
  COUNT(*) AS event_count
FROM login_event
WHERE user_id = ?
  AND logged_in_at >= ?
  AND logged_in_at < ?
GROUP BY
  WEEKDAY(CONVERT_TZ(logged_in_at, 'UTC', ?)),
  HOUR(CONVERT_TZ(logged_in_at, 'UTC', ?))
ORDER BY weekday_index, hour_of_day;

As with the daily histogram, fill missing weekday-hour combinations before visualization. Comparing complete weeks also avoids bias from a partial week at either edge of the observation period.

Interpret the Result and Protect Privacy

The strongest output is a distribution, not a sentence claiming that a person is "mostly online" at a particular time. Report the time zone, date range, event count, and measured event type with the histogram or active window. Recompute periodically because routines change.

For aggregate product analytics, prefer cohorts large enough to avoid exposing an individual's routine. Retain only the event history needed for a stated purpose, restrict access, and consider storing pre-aggregated hourly counts instead of long-lived raw login records. The SQL can identify patterns; it cannot decide whether collecting or using them is justified.