Member Avatar for Silent-Regret

Okay, I have a table called InGameTime with the following columns:
CharName varchar(30)
InTime datetime
OutTime datetime
HH int
MM int
SS int


I am trying to make a trigger, that will once data is updated, it will update the HH,MM,SS to add the total hours, minutes, seconds spent in game. I can get as far as making it add everything, but it adds everything entirely, I want it to be capped off at 60 Minutes, 3600 (I believe?) Seconds. So when the minute column reaches 60, it would roll back over to 0 and set Hour +1 etc...

I can try to explain more if needed, and thank you for your time.

  • 3 Contributors
  • 3 Replies
  • 247 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by Silent-Regret

Dani AI

Generated

Short summary and practical recommendation based on the thread: the simplest, most robust option is not to keep separate HH / MM / SS columns but to store a single cumulative value (total seconds) per character and format that for display. pointed toward runtime calculation; that is sound. ’s example is useful to see how to split seconds into parts, but it uses scalar variables and manual decomposition which breaks for multi-row operations. A set-based trigger that aggregates seconds from the inserted rows and writes to a totals table avoids rollover logic, recursion and multi-row bugs.

A compact, set-based pattern:

  • Add a totals table (CharName, TotalSeconds bigint).
  • Use an AFTER INSERT, UPDATE trigger on InGameTime that sums DATEDIFF(SECOND,InTime,OutTime) per CharName from the inserted pseudo-table and then UPDATE/INSERT into the totals table.
  • Keep DATEDIFF and arithmetic inside the trigger (guard OutTime/ InTime NULLs and negative durations) and use bigint for long-running accounts.

Example trigger (set-based, avoids updating the same table to prevent recursion):

CREATE TABLE PlayerTotals (
  CharName   VARCHAR(30) PRIMARY KEY,
  TotalSeconds BIGINT NOT NULL DEFAULT 0
);

CREATE TRIGGER trg_AddPlayTime
ON InGameTime
AFTER INSERT, UPDATE
AS
BEGIN
  SET NOCOUNT ON;

  ;WITH added AS (
    SELECT i.CharName,
           SUM(DATEDIFF(SECOND, i.InTime, i.OutTime)) AS AddedSec
    FROM inserted i
    WHERE i.InTime IS NOT NULL
      AND i.OutTime IS NOT NULL
      AND i.OutTime >= i.InTime
    GROUP BY i.CharName
  )
  UPDATE t
  SET t.TotalSeconds = t.TotalSeconds + a.AddedSec
  FROM PlayerTotals t
  JOIN added a ON t.CharName = a.CharName;

  INSERT INTO PlayerTotals (CharName, TotalSeconds)
  SELECT a.CharName, a.AddedSec
  FROM added a
  LEFT JOIN PlayerTotals t ON t.CharName = a.CharName
  WHERE t.CharName IS NULL;
END;

Formatting for PHP (handles totals > 24h without wrapping):

$hours = floor($totalSeconds / 3600);
$minutes = floor(($totalSeconds % 3600) / 60);
$seconds = $totalSeconds % 60;
echo sprintf('%02d:%02d:%02d', $hours, $minutes, $seconds);

Notes and cautions: use set-based logic so multi-row updates are handled correctly; store times in a consistent timezone (UTC) to avoid DST problems; validate OutTime >= InTime; use bigint for totals to avoid overflow. This approach keeps rollover logic out of presentation and makes aggregation and reporting far simpler.

Recommended Answers

All 3 Replies

Member Avatar for buddylee17

Try modifying the following to pull from the table, rather than comparing the date variables:

DECLARE @intime DATETIME
DECLARE @outime DATETIME
DECLARE @time int--total seconds
DECLARE @days int, @hours tinyint, @minutes tinyint, @seconds tinyint
SET @intime = GETDATE()
SET @outime = DATEADD(S, 3663, @intime)

SET @time = DATEDIFF(S,@intime,@outime)
SET @days = @time / 86400
SET @hours = (@time/3600) - (@days * 24)
SET @minutes = (@time/60) - (@days * 1440) - (@hours * 60)
SET @seconds = @time % 60

SELECT @time total_second_count
	 , @days day_count
	 , @hours hour_count
	 , @minutes minute_count 
	 , @seconds second_count
commented: that will work nicely. +8
Member Avatar for debasisdas

No need to store data in HH, MM, SS columns.

These can be calculated at run time using the previous solution.

Member Avatar for Silent-Regret

Sorry for the long response, and thank you for the answers.

I was trying to make this so that I could pull the 3 columns for a PHP script on our website, to show the total time logged in.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.