SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE TABLE timeslices ( id SERIAL PRIMARY KEY, created_at TIMESTAMPTZ, finished_at TIMESTAMPTZ ); INSERT INTO timeslices (created_at, finished_at) VALUES ( '2022-05-26 10:12:06.720017+00', '2022-05-26 10:15:06.100017+00' ), ( '2022-05-26 07:26:50.715445+00', '2022-05-26 07:30:50.715445+00' ), ( '2022-05-26 17:55:05.123208+00', '2022-05-26 17:59:05.670208+00' ); CREATE TABLE results ( id SERIAL PRIMARY KEY, account TEXT NOT NULL, created_at TIMESTAMPTZ, timeslice BIGINT ); INSERT INTO results (account, created_at) VALUES ( 'first', '2022-05-26 10:12:06.720017+00' ), ( 'first', '2022-05-26 07:27:50.715445+00' ), ( 'first', '2022-05-26 14:55:28.434542+00' ), ( 'first', '2022-05-26 17:55:05.670208+00' ), ( 'first', '2022-05-26 17:56:05.670208+00' ); SELECT * FROM timeslices; SELECT * FROM results; WITH sub AS ( SELECT ts.id AS timeslice FROM results r LEFT JOIN timeslices ts ON r.created_at BETWEEN ts.created_at AND ts.finished_at ) UPDATE results r SET r.timeslice = s.id FROM sub s
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear