Pgsql find similar records in database

I have a table that has the layout of this

sensor_id	time	value
1	2020-12-22 09:00:00	20.5
1	2020-12-22 10:00:00	21.5
1	2020-12-22 11:00:00	22.5
1	2020-12-22 12:00:00	23.5
2	2020-12-22 09:00:00	30.5
2	2020-12-24 10:00:00	31.5
2	2020-12-24 11:00:00	32.5
2	2020-12-24 12:00:00	33.5

I want to be able to do is find all references where sensor_id 1 and 2 have the same date.

What the plan is is to have a user select a station, which contains a list of sensors, to move data from station a to station b. If there is any data on station a that already exists for this time on station b, then i don’t need to move it. I plan on ‘moving’ the data by running a simple update readings set sensor_id = #a where sensor_id = #b.

Note, the actual data I have billions of records, and I will be ‘moving’ data from one sensor_id to another, and often there will be a list of about 10 sensors to move at one time.

I don’t really know where to start on this. Can anyone point me in the right direction?

Answer

find all references where sensor_id 1 and 2 have the same date

For this you can use aggregation:

select time
from readings
when sensor_id in (1, 2)
group by time
having count(*) = 2

This gives you all times for which both sensors have data.

From the rest of the question, I think that you want to change the sensor id (say, from 1 to 2) on records when it will not conflict with an existing record. This suggests:

update readings r
set sensor_id = 2
where sensor_id = 1 and not exists (
    select 1 from readings r1 where r1.sensor_id = 2 and r1.time = r.time
)