Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table tbl (datetime timestamptz, s_1 float, s_2 float);
insert into tbl values 
  ('20230101T10:00:00Z', 3.00, null),
  ('20230101T11:00:00Z', 2.00, 10.0),
  ('20230101T12:00:00Z', 1.00, null),
  ('20230101T23:00:00Z', 4.00, null),
  ('20230102T10:00:00Z', 2.00, null),	
  ('20230102T10:00:00Z', 1.00, 9.3)

CREATE TABLE



INSERT 0 6


SELECT 
    date (timezone('Europe/Berlin', datetime))::timestamptz as datetime, 
    MAX (s_1) as s_1_max, 
    MAX (s_2) as s_2_max
FROM tbl
GROUP BY date (timezone('Europe/Berlin', datetime))


datetime
s_1_max
s_2_max


2023-01-01 00:00:00+00
3
10

2023-01-02 00:00:00+00
4
9.3



SELECT 2


select datetime, col, val from (
  select datetime, col, val, 
         rank() over (partition by dt, col order by val desc) rnk 
  from ( 
    select datetime,
           date(timezone('Europe/Berlin', datetime)) dt,
           unnest(array['s_1', 's_2']) as col,
           unnest(array[s_1, s_2]) as val
    from tbl t) unpivoted
  where val is not null ) ranked
where rnk = 1


datetime
col
val


2023-01-01 10:00:00+00
s_1
3

2023-01-01 11:00:00+00
s_2
10

2023-01-01 23:00:00+00
s_1
4

2023-01-02 10:00:00+00
s_2
9.3



SELECT 4

datetime	col	val
2023-01-01 10:00:00+00	s_1	3
2023-01-01 11:00:00+00	s_2	10
2023-01-01 23:00:00+00	s_1	4
2023-01-02 10:00:00+00	s_2	9.3