SQLize Online / PHPize Online  /  SQLtest Online

create table game_sessions( id integer, "isFinished" boolean, createdAt timestamp without time zone NOT NULL DEFAULT ('2023-02-06') ); create table game_transactions( id integer, "sessionId" integer, createdAt timestamp without time zone NOT NULL DEFAULT (current_timestamp AT TIME ZONE 'UTC') ); insert into game_sessions(id, "isFinished") values(1, true); insert into game_sessions(id, "isFinished") values(2, false); insert into game_sessions(id, "isFinished") values(3, true); insert into game_sessions(id, "isFinished") values(4, false); insert into game_transactions(id, "sessionId", createdAt) values(1, 1, '2023-02-07 07:47:02'); insert into game_transactions(id, "sessionId", createdAt) values(2, 4, '2023-02-07 06:47:02'); insert into game_transactions(id, "sessionId", createdAt) values(3, 5, '2023-02-07 02:47:02'); insert into game_transactions(id, "sessionId", createdAt) values(4, 2, '2023-02-07 07:47:02'); select * from game_sessions; select * from game_transactions; select (NOW() - interval '3 hour'); select * from game_sessions gs inner join game_transactions gt on gt."sessionId" = gs.id and gt.createdAt > (NOW() - interval '3 hour') where gs."isFinished" = false; update game_sessions set "isFinished" = true from game_sessions gs left join game_transactions gt on gt."sessionId" = gs.id and gt.createdAt < (NOW() - interval '3 hour') where gs."isFinished" = false and gs.createdAt < (NOW() - interval '3 hour'); select * from game_sessions;