I have a query that should return some rows, but it returns blank. As far as I know .. the pictures down will show the issue.
Here is the query
SELECT * from matches m WHERE M.PLAYED_AT BETWEEN (M.PLAYED_AT - M.STARTED_PREDICTION) AND (M.PLAYED_AT - M.ENDED_PREDICTION)
This is the table :
-- Table Definition ---------------------------------------------- CREATE TABLE matches ( id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, deleted_at timestamp with time zone, started_prediction interval NOT NULL DEFAULT '48:00:00'::interval, ended_prediction interval NOT NULL DEFAULT '02:00:00'::interval, played_at timestamp with time zone NOT NULL ); -- Indices ------------------------------------------------------- CREATE UNIQUE INDEX matches_pkey ON matches(id int4_ops);
I tested the calculation and it worked a lone .
SELECT id, (M.PLAYED_AT - M.STARTED_PREDICTION) started , (M.PLAYED_AT - M.ENDED_PREDICTION) ended from matches m;
Advertisement
Answer
Your condition cannot be right because you are comparing M.PLAYED_AT to be
BETWEEN M.PLAYED_AT - X AND M.PLAYED_AT - Y
– this can never be because you are comparing a timestamp with a previous timestamp and another previous timestampt.
Probably you meant to do BETWEEN ... AND (M.PLAYED_AT + M.ENDED_PREDICTION)