How to select data between two datetimes which are related to certain ID in postgresql

user_18910343 0
Viewed 19

So I have 2 tables. In first table I need to link ID with min and max datetime, which I did with

SELECT id, min (datetime) as earlytime, max(datetime) as latesttime
FROM table1
group by id

as a result I get around 5k with ID and 2 timestamps.

I need to use this info to get rows with ID from table2 where datetime for each ID respectively is between their earlytime and latesttime. Or just rows with ID and time which less than latesttime.

Still can't figure out how to do that.

Came up with something like that

SELECT ID, source, amount, type, datetime
FROM table2
WHERE EXISTS (SELECT ID, min (datetime) as earlytime, max (datetime) as latetime
FROM table1
group by ID)

But I guess it just shows any rows that match ID from table1

Anyone can help me with that?

postgresql sql
3 Answers

maybe you can join and get the results like below

select A.* 
from table2 A 
join 
(
SELECT id, min (datetime) as earlytime, max(datetime) as latesttime
FROM table1
group by id
)B
on A.id=B.id and 
B.timecol between earlytime and latesttime
user_11232260

You can use a combination of a CTE or sub-query (whichever you prefer) and BETWEEN()to achieve your expected output

with cte as (
   select
      id
      ,min(datetime) as earlytime
      ,max(datetime) as latesttime
   from table1
   group by id
)

select
   c.id
   ,c.earlytime
   ,c.latesttime
   ,t2.* /*Table2 columns*/
from cte as c
inner join table2 as t2 ON c.id = t2.id 
  and t2.datetime between c.earlytime and c.latesttime
user_110143100 
SELECT T2.ID,T2.SOURCE,T2.AMOUNT,T2.TYPE,T2.DATETIME
FROM TABLE2 AS T2
JOIN
(
  SELECT id, min (datetime) as earlytime, max(datetime) as latesttime
  FROM table1
  group by id
)X ON T2.ID=X.ID AND T2.DATETIME BETWEEN X.earlytime AND X.latesttime
user_145355170
Related