user3567212 user3567212 - 3 months ago 18
MySQL Question

MySQL merge rows with same ID while preserving timestamps

I have the following table with data:

Table 1:

ID START_DATE END_DATE
-- ---------- --------
1 2016-08-20 00:01:00 NULL
1 2016-08-20 00:02:00 NULL
1 NULL 2016-08-20 00:03:00
1 NULL 2016-08-20 00:04:00


I would like to "merge" the data for the unique ID 1 so that I can easily read it. It should look like the following (basically grabbing the earliest timestamp for START_DATE and latest timestamp for END_DATE):

Table 2:

ID START_DATE END_DATE
-- ---------- --------
1 2016-08-20 00:01:00 2016-08-20 00:04:00


This is ultimately so that I can determine if the following TX_ID record falls within the START_DATE and END_DATE and set the FALL_WITHIN_START_AND_END parameter to True.

Table 3:

TX_ID TIMESTAMP FALL_WITHIN_START_AND_END
----- --------- -------------------------
123456 2016-08-20 00:02:35 True


Could someone recommend a MySQL query to "merge" Table 1 value so that it would look like Table 2? This is so I can more easily determine if the TX_ID in Table 3 falls within the complete start and end date range.

Thank you in advance for your help!

Answer

Barmar has a fine approach for the first part of your question. But, for the full answer, you can use exists:

select t3.*,
       (case when exists (select 1
                          from table1 t1
                          where t1.id = t3.id and t1.start_date <= t3.timestamp
                         ) and
                  exists (select 1
                          from table1 t1
                          where t1.id = t3.id and t1.end_date >= t3.timestamp
                         ) 
             then 1 else 0
        end) as fall_within_start_and_end 
from table3 t3;

The advantage of this approach over the group by is that it can take advantage of indexes on table1(id, start_date) and table1(id, end_date).

Comments