Postgres query return nothing where there should be a valid data-CodePudding

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;

CodePudding user response：

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)

CodePudding user response：

I found the issue, it should be now() instead of played_At .

SELECT * from matches m WHERE NOW() BETWEEN (M.PLAYED_AT - M.STARTED_PREDICTION) AND (M.PLAYED_AT - M.ENDED_PREDICTION)