finding date intervals start and end dates

Question

I have a table like this (plus 10 more columns) containing more than 1 million of frequently updated records:

 id pid start_date          end_date  
  1 761 2011-07-25 00:00:00 2011-08-01 00:00:00
  2 761 2011-08-01 00:00:00 2011-08-22 00:00:00
  3 761 2011-08-22 00:00:00 2011-09-19 00:00:00
  4 802 2011-08-22 00:00:00 2011-09-19 00:00:00
  5 761 2011-06-05 00:00:00 2011-07-05 00:00:00

and would like to get result for a particular pid (761 in the example below) with all consecutive intervals combined:

 id pid start_date          end_date  
  1 761 2011-07-25 00:00:00 2011-09-19 00:00:00
  5 761 2011-06-05 00:00:00 2011-07-05 00:00:00

Currently I am doing this in the code, but would like to move this functionality entirely to the db side. Any ideas how to do this?

edit: start_date and end_date columns are of DATETIME type.

Do you have the `pid` value at the time you will run the query? — Shef, Jul 17 '11 at 18:44
@Shef - yes, I do have the pid value when running the query. — orom, Jul 17 '11 at 18:47

score 0 · Accepted Answer · answered Jul 17 '11 at 18:42

0

This is really much better done in code. Loop over the rows, when it's for the same product, update the end date, otherwise create a new array entry.

For an idea of how complex this is in SQL, see my attempt at solving this in SQL Server :)

answered Jul 17 '11 at 18:42

Andomar

232,371
49
380
404

It does look complex from a performance pov. Also i am not sure how easily can it be ported to mysql, but it did gave me a number of hints. Thanks! – orom Jul 17 '11 at 19:02

finding date intervals start and end dates

1 Answers1

Linked