My table is called platform_statuses
, here is its schema:
CREATE TABLE platform_statuses ( id SERIAL, account INTEGER REFERENCES users (id), time TIMESTAMP DEFAULT NOW(), value_in_cents INTEGER NOT NULL, status INTEGER NOT NULL CHECK ( 0 < status AND status < 4 ) DEFAULT 2, -- 1: Active, 2: Suspended, 3: Market is closed open_trades INTEGER NOT NULL, PRIMARY KEY (id) );
And this is my query, I would like to also get the matching id for the returned records.
SELECT max(timediff), time :: date, account FROM ( SELECT id, time, account, abs(time - date_trunc('day', time + '12 hours')) as timediff FROM platform_statuses ) AS subquery GROUP BY account, time :: date
Also note that the abs function you see in the query is a custom one I got off this answer. Here is its definition:
CREATE FUNCTION abs(interval) RETURNS interval AS $ $ SELECT CASE WHEN ($ 1 < interval '0') THEN - $ 1 else $ 1 END; $ $ LANGUAGE sql immutable;
Advertisement
Answer
I understand that, for each account and day, you want the record with the greatest timediff
. If so, you can use distinct on()
directly on your existing subquery:
select distinct on (account, time::date) id, time, account, abs(time - date_trunc('day', time + '12 hours')) as timediff from platform_statuses order by account, time::date, timediff desc