Postgres 13

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

CREATE TABLE T (
   name varchar,
   value int,
   timestamp timestamp
);
insert into T values 
  ('1.aa', 1, '2021-08-20 10:10:01'),
	('2.aa', 2, '2021-08-20 10:10:01'),
	('3.aa', 3, '2021-08-20 10:10:01'),
	('1.bb', 4, '2021-08-20 10:10:01'),
	('2.bb', 5, '2021-08-20 10:10:01'),
	('3.bb', 6, '2021-08-20 10:10:01'),
	('1.aa', 7, '2021-08-20 10:10:02'),
	('2.aa', 8, '2021-08-20 10:10:02'),
	('3.aa', 9, '2021-08-20 10:10:02'),
	('1.bb', 0, '2021-08-20 10:10:02'),
	('2.bb', 1, '2021-08-20 10:10:02'),
	('3.bb', 2, '2021-08-20 10:10:02');
12 rows affected

with cte as (
  select split_name[1] as id, 
  		split_name[2] as name, 
  		value, 
  		timestamp::date as date, 
  		timestamp::time as time 
  from (
		select 
			regexp_split_to_array(name, '\.') split_name, 
    		timestamp,
			value
		from T
	) foo
  )
select id, date, time, a.value as aa, b.value as bb from cte a
left join (
	select * from cte where name = 'bb'
) as b using (id, date, time)
where a.name = 'aa'


id
date
time
aa
bb


1
2021-08-20
10:10:01
1
4

2
2021-08-20
10:10:01
2
5

3
2021-08-20
10:10:01
3
6

1
2021-08-20
10:10:02
7
0

2
2021-08-20
10:10:02
8
1

3
2021-08-20
10:10:02
9
2

id	date	time	aa	bb
1	2021-08-20	10:10:01	1	4
2	2021-08-20	10:10:01	2	5
3	2021-08-20	10:10:01	3	6
1	2021-08-20	10:10:02	7	0
2	2021-08-20	10:10:02	8	1
3	2021-08-20	10:10:02	9	2