Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tbl(account_id int, timestamp timestamp, balance_one numeric, balance_two numeric);
INSERT INTO tbl VALUES
    ('1', '28 June 2021 17:00:00', '0.0', '0.0'),
    ('1', '28 June 2021 17:00:05', '1.0', '0.0'),
    ('1', '28 June 2021 17:00:10', '0.5', '0.0'),
    ('1', '28 June 2021 17:00:15', '0.0', '1.0'),
    ('1', '28 June 2021 17:00:20', '0.5', '0.0'),
    ('1', '28 June 2021 17:00:25', '1.0', '1.0'),
    ('2', '28 June 2021 17:00:00', '0.0', '0.0'),
    ('2', '28 June 2021 17:00:05', '1.0', '7.0'),
    ('2', '28 June 2021 17:00:07', '2.0', '6.0'),
    ('2', '28 June 2021 17:00:15', '3.0', '5.0'),
    ('2', '28 June 2021 17:00:20', '4.0', '4.0'),
    ('2', '28 June 2021 17:00:25', '5.0', '3.0'),
    ('2', '28 June 2021 17:00:30', '6.0', '2.0'),
    ('2', '28 June 2021 17:00:35', '7.0', '1.0');

CREATE TABLE



INSERT 0 14


-- original example 2
SELECT DISTINCT (COALESCE(p.t, n.t)).*
FROM   generate_series(timestamp '2021-06-28 17:00:00'  -- lower bound of time frame
                     , timestamp '2021-06-28 17:00:30'  -- upper bound of time frame
                     , interval '10 sec') AS g(ts)      -- step size
LEFT   JOIN LATERAL (
   SELECT t.timestamp, t
   FROM   tbl t
   WHERE  account_id = 2
   AND    t.timestamp >= g.ts
   AND    t.timestamp BETWEEN timestamp '2021-06-28 17:00:00'
                      AND     timestamp '2021-06-28 17:00:30'  -- enforce time frame
   ORDER  BY t.timestamp
   LIMIT  1
   ) n ON true
LEFT   JOIN LATERAL (
   SELECT t.timestamp, t
   FROM   tbl t
   WHERE  account_id = 2
   AND    t.timestamp < g.ts
   AND    t.timestamp BETWEEN timestamp '2021-06-28 17:00:00'
                      AND     timestamp '2021-06-28 17:00:30'  -- enforce time frame
   ORDER  BY t.timestamp DESC
   LIMIT  1
   ) p ON g.ts - p.timestamp < n.timestamp - g.ts
       OR n.timestamp IS NULL   -- suppress NULL row for no later match
WHERE  n.timestamp IS NOT NULL
    OR p.timestamp IS NOT NULL  -- suppress NULL row for no match at all
ORDER  BY timestamp;


account_id
timestamp
balance_one
balance_two


2
2021-06-28 17:00:00
0.0
0.0

2
2021-06-28 17:00:15
3.0
5.0

2
2021-06-28 17:00:20
4.0
4.0

2
2021-06-28 17:00:30
6.0
2.0



SELECT 4


-- CTE variant
WITH cte AS (
   SELECT * FROM tbl
   WHERE  account_id = 2
   AND    timestamp BETWEEN timestamp '2021-06-28 17:00:00'  -- lower bound of time frame
                    AND     timestamp '2021-06-28 17:00:30'  -- upper bound of time frame
   )
SELECT DISTINCT (COALESCE(p.t, n.t)).*
FROM   generate_series(timestamp '2021-06-28 17:00:00'  -- lower bound
                     , timestamp '2021-06-28 17:00:30'  -- upper bound
                     , interval  '10 sec') AS g(ts)     -- step size
LEFT   JOIN LATERAL (
   SELECT t.timestamp, t::tbl
   FROM   cte t
   WHERE  t.timestamp >= g.ts
   ORDER  BY t.timestamp
   LIMIT  1
   ) n ON true
LEFT   JOIN LATERAL (
   SELECT t.timestamp, t::tbl
   FROM   cte t
   WHERE  t.timestamp < g.ts
   ORDER  BY t.timestamp DESC
   LIMIT  1
   ) p ON g.ts - p.timestamp < n.timestamp - g.ts
       OR n.timestamp IS NULL   -- suppress NULL row for no later match
WHERE  n.timestamp IS NOT NULL
    OR p.timestamp IS NOT NULL  -- suppress NULL row for no match at all
ORDER  BY timestamp;


account_id
timestamp
balance_one
balance_two


2
2021-06-28 17:00:00
0.0
0.0

2
2021-06-28 17:00:15
3.0
5.0

2
2021-06-28 17:00:20
4.0
4.0

2
2021-06-28 17:00:30
6.0
2.0



SELECT 4
account_id	timestamp	balance_one	balance_two
2	2021-06-28 17:00:00	0.0	0.0
2	2021-06-28 17:00:15	3.0	5.0
2	2021-06-28 17:00:20	4.0	4.0
2	2021-06-28 17:00:30	6.0	2.0