PostgreSQL: How do I join two tables based on same start and end time (timestamp without time zone)?

Question

Okay, I came across this relevant question but it is slightly different than my case.

Problem

I have two similar type of tables in my PostgreSQL 9.5 database tbl1 and tbl2 both containing 1,274 rows. The structure and layout of table 1 is as follows:

Table 1:

id (integer)   start_time           end_time              my_val1 (numeric)
51             1994-09-26 16:50:00  1994-10-29 13:30:00   3.7
52             1994-10-29 13:30:00  1994-11-27 12:30:00   2.4
53             1994-11-27 12:30:00  1994-12-29 09:25:00   7.6
54             1994-12-29 09:25:00  1994-12-31 23:59:59   2.9
54             1995-01-01 00:00:00  1995-02-05 13:50:00   2.9
55             1995-02-05 13:50:00  1995-03-12 11:10:00   1.6
56             1995-03-12 11:10:00  1995-04-11 09:05:00   2.2
171            1994-10-29 16:15:00  1994-11-27 19:10:00   6.9
172            1994-11-27 19:10:00  1994-12-29 11:40:00   4.2
173            1994-12-29 11:40:00  1994-12-31 23:59:59   6.7
173            1995-01-01 00:00:00  1995-02-05 15:30:00   6.7
174            1995-02-05 15:30:00  1995-03-12 09:45:00   3.2
175            1995-03-12 09:45:00  1995-04-11 11:30:00   1.2
176            1995-04-11 11:30:00  1995-05-11 15:30:00   2.7
321            1994-09-26 14:40:00  1994-10-30 14:30:00   0.2
322            1994-10-30 14:30:00  1994-11-27 14:45:00   7.8
323            1994-11-27 14:45:00  1994-12-29 14:20:00   4.6
324            1994-12-29 14:20:00  1994-12-31 23:59:59   4.1
324            1995-01-01 00:00:00  1995-02-05 14:35:00   4.1
325            1995-02-05 14:35:00  1995-03-12 11:30:00   8.2
326            1995-03-12 11:30:00  1995-04-11 09:45:00   1.2
.....

In some rows, start_time and end_time may look similar but whole time window may not be equal. For example,

 id (integer)   start_time          end_time              my_val1 (numeric)
 54             1994-12-29 09:25:00 1994-12-31 23:59:59   2.9
 173            1994-12-29 11:40:00 1994-12-31 23:59:59   6.7

Start_time and end_time are timestamp without time zone. The start_time and end_time have to be in one year window thus whenever there was a change of year from 1994 to 1995 then that row was divided into two rows therefore, there are repeating IDs in the column id. Table 2 tbl2 contains the similar start_time and end_time (timestamp without time zone) and column my_val2 (numeric). For each row in table 1 I need to join corresponding row of table 2 where start_time and end_time are similar.

What I have tried,

Select 
    a.id,
    a.start_time, a.end_time,
    a.my_val1,
    b.my_val2
from tbl1 a
left join tbl2 b on 
b.start_time = a.start_time
order by a.id;

The query returned 3,802 rows which is not desired. The desired result is 1,274 rows of table 1 joined with my_val2. I am aware of Postgres Distinct on clause but I need to keep all repeating ids of tbl1 and only need to join my_val2 of tbl2. Do I need to use Postgres Window function here. Can someone suggest that how to join these two tables?

If the start and end time in tbl2 and tbl1 are similar should you join on start_time and end_time? Also how similar are the times for tbl1.start and tbl2.start? Will equals give the desired result or should some rounding be applied to the times? — WJS, Sep 05 '17 at 20:14
I tried: b.start_time = a.start_time AND b.end_time = a.end_time but query returned some empty rows. The problem is IDs only exist in table_1. In table 2, start_time and end_time window may be the same but they are not in the same order as table_1. — khajlk, Sep 05 '17 at 20:20
Executing Distinct on start_time and end_time in both tables returned 1,187 rows. Thus, start and end_time are not unique like IDs. I am not sure rounding will help or give desired result. I can't show all start and end_time rows here. — khajlk, Sep 05 '17 at 20:22

score 0 · Answer 1 · answered Sep 05 '17 at 20:14

0

why you don't add to the ON part the condition

ON b.start_time = a.start_time AND a.id = b.id

answered Sep 05 '17 at 20:14

Samer Abu Gahgah

751
1
9
18

Thanks. But, IDs column only exists in table_1. – khajlk Sep 05 '17 at 20:21
You don't have a primary key neither a forgin key on your tables, so how do you know which rows are connected to each other? – Samer Abu Gahgah Sep 05 '17 at 20:52
As I said above, the start_time and end_time window are same but may not be in the same order. I cab create object_id as a new primary key column but I'm not sure the join will give the desired result. – khajlk Sep 05 '17 at 20:59

score 0 · Answer 2 · answered Sep 05 '17 at 22:28

For each row in table 1 I need to join corresponding row of table 2 where start_time and end_time are similar.

SQL query should include end_time

SELECT a.id,
       a.start_time, 
       a.end_time,
       a.my_val1,
       b.my_val2
  FROM tbl1 a
  LEFT JOIN tbl2 b 
    ON b.start_time = a.start_time
   AND b.end_time = a.end_time
 ORDER BY a.id;

PostgreSQL: How do I join two tables based on same start and end time (timestamp without time zone)?

2 Answers2