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.
• Events, as they happen: every kill (weapon, headshot, no-scope, through smoke, wallbang, blind, victim), bomb plant and bomb defuse (0.2.0+).
• 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 as playtime like any other time, but not toward match stats (kills, deaths, assists, headshots, damage, MVPs, score, rounds) or events (0.2.0+; before that it counted for everything). Bots and SourceTV are ignored, except that a kill of a bot is logged with victim_bot 1.

Setup

1. 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.

2. 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.

3. 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.

Match stats come from the game's own per-player counters, read every second. Only what they gain outside warmup counts, so the session's totals leave warmup out. player_death, bomb_planted and bomb_defused are logged to pt_events the moment they happen, except in warmup. A kill is logged for a tracked killer; suicides and team kills aren't.

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_events	kind 1 kill, 2 bomb planted, 3 bomb defused; steamid64 is who did it, with at, map, team and session_key. Kills add weapon (as the game names it: taser is the Zeus, knives are knife, knife_t, bayonet or knife_<skin>), headshot, noscope, thrusmoke, wallbang, blind, victim_steamid64 and victim_bot. Plants and defuses add site.
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';

The most bomb plants, defuses, knife kills, Zeus kills and headshots in the last 7 days (kills of bots left out):

SELECT p.last_nickname, e.steamid64,
       SUM(e.kind = 2) AS plants, SUM(e.kind = 3) AS defuses,
       SUM(e.kind = 1 AND (e.weapon LIKE 'knife%' OR e.weapon = 'bayonet') AND NOT e.victim_bot) AS knife_kills,
       SUM(e.kind = 1 AND e.weapon = 'taser' AND NOT e.victim_bot) AS zeus_kills,
       SUM(e.kind = 1 AND e.headshot AND NOT e.victim_bot) AS headshots
FROM pt_events e JOIN pt_players p USING (steamid64)
WHERE e.at >= UTC_TIMESTAMP() - INTERVAL 7 DAY
GROUP BY e.steamid64
ORDER BY plants DESC
LIMIT 10;

Who's online now:

SELECT s.nickname, s.steamid64, s.started_at, g.team, g.alive
FROM pt_sessions s LEFT JOIN pt_segments g ON g.session_key = s.session_key AND g.ended_at IS NULL
WHERE s.server_id = 1 AND s.ended_at IS NULL;

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/.