PlaytimeHistory

Records every player's time on the server in its own MySQL/MariaDB database:

Sessions: join to leave (or to a map change), with the map, disconnect reason, country and city, match stats (kills, deaths, assists, headshots, damage, MVPs, score), rounds played and ping.
Segments inside each session: stretches where the player's team (unassigned, spectator, T, CT) and alive/dead state didn't change. Per-hour, per-team, alive/dead and spectator time all come from these with SQL; see Queries.
Running totals per player, nickname history, and a count of players online every 5 minutes.

It replaces ConnectHistory. Player IPs are never stored: the IP is only used, on join, to look up a country and city.

Warmup counts like any other time. Bots and SourceTV are ignored.

Setup

Create the database and a user for the plugin, as root in MariaDB. On fr04 the cs2 container reaches MariaDB from 10.89.0.1 (the systemd-static network's gateway), so that's the host:
```
CREATE DATABASE playtime_history CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'pt_plugin'@'10.89.0.1' IDENTIFIED BY '<strong password>';
GRANT SELECT, INSERT, UPDATE, CREATE, INDEX, ALTER, CREATE VIEW ON playtime_history.* TO 'pt_plugin'@'10.89.0.1';
```
It never needs DELETE or DROP. For something that only reads it (the web panel), a separate user with GRANT SELECT ON playtime_history.* is enough, the hourly view included.

Start the server once. The plugin writes configs/plugins/PlaytimeHistory/PlaytimeHistory.json (mode 600) and records nothing until Database.Password is filled in:

{
  "Database": {
    "Host": "10.89.0.25",
    "Port": 3306,
    "Database": "playtime_history",
    "User": "pt_plugin",
    "Password": "<the password>",
    "SslMode": "None"
  },
  "ServerId": 1,
  "PublicAddress": "162.19.125.4:27015",
  "CountSpectatorTime": false,
  "GeoIpDatabase": "dbip-city-lite.mmdb",
  "PingSampleIntervalSeconds": 30,
  "OnlineSnapshotIntervalSeconds": 300,
  "HeartbeatSeconds": 60,
  "SpoolMaxEntries": 20000,
  "OtherPlayersPermission": "@css/generic",
  "CommandCooldownSeconds": 5,
  "LastSeenLimit": 5,
  "ConfigVersion": 1
}

SslMode None is fine on fr04, where the connection never leaves the host's podman networks.

css_plugins reload PlaytimeHistory, then css_pt_status should say Database: ok. The tables are created on first connect.

Setting	Meaning
`ServerId`	`pt_servers.id`. Must differ between servers sharing a database.
`PublicAddress`	Stored in `pt_servers.address`; the server can't work out its own public address.
`CountSpectatorTime`	Whether spectating and unassigned time counts toward `pt_players.total_seconds` and the `css_playtime` total. It's recorded either way.
`GeoIpDatabase`	The location database, relative to the plugin folder. Missing: no country or city.
`HeartbeatSeconds`	How often open sessions and segments are marked as still going. After a crash they're closed at their last heartbeat, so at most this much time is lost.
`SpoolMaxEntries`	Writes kept on disk while the database is down (see below).
`OtherPlayersPermission`	Needed to look up anyone but yourself.

Commands

Command	Who	What
`css_playtime [player]`	everyone (others: `OtherPlayersPermission`)	Total, T/CT/spectating, alive/dead, last 7 days
`css_lastseen [player]`	everyone (others: `OtherPlayersPermission`)	Recent sessions
`css_pt_status`	`@css/root`	Database state, queued and spooled writes, location database

[player] is part of an online player's name, #userid, or a SteamID64 (online or not). From the server console the answer to css_playtime/css_lastseen is printed to the console a moment later, so it doesn't reach RCON; read the database for that.

How it records

Every second, and right after player_team, player_spawn, player_death and player_connect_full, the plugin reads each player's team and whether their pawn is alive. A change closes the current segment and opens the next, so segment times are exact to the second. Sessions close on player_disconnect (with the reason), on map end and on unload.

Database writes run in order on one background task, never on the game thread. When the database is unreachable (or refuses the login), writes go to plugins/PlaytimeHistory/spool.jsonl and are sent, in order, once it's back; css_pt_status shows how many are waiting. Writes are keyed, so sending one twice changes nothing. A write the database rejects outright (bad data) is logged and dropped.

If the server dies with sessions open, the next start closes them at their last heartbeat with end_kind 4.

Tables

All times are UTC (UTC_TIMESTAMP(), not NOW(), when comparing).

Table	What
`pt_sessions`	One row per connection. `end_kind`: 0 open, 1 disconnect, 2 map change, 3 plugin unload, 4 stale (server died). `duration_seconds` is connected time.
`pt_segments`	`team` (0 unassigned, 1 spectator, 2 T, 3 CT), `alive` (1/0, NULL off T/CT), `started_at`, `ended_at`, `seconds`, `map`, `session_key`. Open while `ended_at IS NULL`.
`pt_segments_hourly`	View: closed segments split at UTC hour boundaries, one row per segment per hour, with `hour_start` and `seconds`.
`pt_players`	Totals per player: `total_seconds`, `t_seconds`, `ct_seconds`, `spec_seconds` (spectator + unassigned), `alive_seconds`, `dead_seconds`, `sessions_count`, first/last seen, last nickname and country. Only closed segments are in them.
`pt_nicknames`	Every name a SteamID has used, with first/last seen and times seen.
`pt_servers`	`id`, `address`, `hostname`, first/last seen.
`pt_online_snapshots`	Players (humans), bots and slots online, every `OnlineSnapshotIntervalSeconds`.
`pt_migrations`	Applied migrations.

Queries

Playtime per hour for one player, by state:

SELECT hour_start, team, alive, SUM(seconds) AS seconds
FROM pt_segments_hourly
WHERE steamid64 = ? AND hour_start >= UTC_TIMESTAMP() - INTERVAL 7 DAY
GROUP BY hour_start, team, alive
ORDER BY hour_start, team, alive;

Time on a team per hour of the day, everyone, last 30 days (team IN (0, 1) for spectating):

SELECT HOUR(hour_start) AS hour_utc, ROUND(SUM(seconds) / 3600, 1) AS hours
FROM pt_segments_hourly
WHERE team IN (2, 3) AND hour_start >= UTC_TIMESTAMP() - INTERVAL 30 DAY
GROUP BY HOUR(hour_start)
ORDER BY hour_utc;

The view splits every closed segment before filtering. If it gets slow, split from pt_segments with the date filter inside instead:

WITH RECURSIVE s AS (
  SELECT steamid64, team, alive, started_at AS t0, ended_at AS t1
  FROM pt_segments
  WHERE ended_at IS NOT NULL AND ended_at > started_at
    AND started_at >= UTC_TIMESTAMP() - INTERVAL 7 DAY
),
h AS (
  SELECT steamid64, team, alive, t1,
         CAST(DATE_FORMAT(t0, '%Y-%m-%d %H:00:00') AS DATETIME) AS hour_start, t0 AS piece_start
  FROM s
  UNION ALL
  SELECT steamid64, team, alive, t1, hour_start + INTERVAL 1 HOUR, hour_start + INTERVAL 1 HOUR
  FROM h WHERE hour_start + INTERVAL 1 HOUR < t1
)
SELECT steamid64, hour_start, team, alive,
       SUM(TIMESTAMPDIFF(SECOND, piece_start, LEAST(t1, hour_start + INTERVAL 1 HOUR))) AS seconds
FROM h
GROUP BY steamid64, hour_start, team, alive;

Leaderboard, time on a team:

SELECT last_nickname, steamid64, ROUND((t_seconds + ct_seconds) / 3600, 1) AS hours,
       ROUND(alive_seconds / GREATEST(t_seconds + ct_seconds, 1) * 100) AS pct_alive
FROM pt_players
ORDER BY t_seconds + ct_seconds DESC
LIMIT 50;

T, CT, spectating, alive and dead in a date range (pt_players is all-time):

SELECT SUM(IF(team = 2, seconds, 0)) AS t, SUM(IF(team = 3, seconds, 0)) AS ct,
       SUM(IF(team IN (0, 1), seconds, 0)) AS spectating,
       SUM(IF(alive = 1, seconds, 0)) AS alive, SUM(IF(alive = 0, seconds, 0)) AS dead
FROM pt_segments
WHERE steamid64 = ? AND ended_at IS NOT NULL
  AND started_at >= '2026-10-01' AND started_at < '2026-11-01';

Location data

Country and city come from DB-IP's free IP to City Lite database (https://db-ip.com/db/download/ip-to-city-lite), licensed CC BY 4.0. Anywhere that shows them (the web panel, say) must credit it: IP Geolocation by <a href="https://db-ip.com">DB-IP</a>.

server/pre.sh installs it into the plugin folder as dbip-city-lite.mmdb (about 60 MB to download, 127 MB unpacked), pinned by month in DBIP_VERSION (YYYY-MM). DB-IP publishes a new one on the 1st of each month; bump DBIP_VERSION by hand to update. The plugin reads it on load, so reload or restart after a new one.

Releases

./release.sh <tag> (with FORGEJO_TOKEN) rebuilds from the committed source and publishes PlaytimeHistory-<tag>.tar.gz, laid out like game/csgo/. The tag must be v<ModuleVersion>. It ships the plugin and its libraries (MySqlConnector, MaxMind.Db), never configs/ or the location database. server/pre.sh installs it (PTH_VERSION).

Build locally with ../../build.sh from this folder; output goes to compiled/PlaytimeHistory/.

9.3 KiB Raw Permalink Blame History