How do I get the matching id for every record?

Question

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;

Answer 1

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