0

I need to split one date record into a several date records (dynamically, depending on a date range that overlaps) like this in MySQL:

input data:

level    |start_time              |end_time

1         2018-12-24 09:00:00     2018-12-25 09:00:00

Output result should look like:

level    |start_time              |end_time
1         2018-12-24 09:00:00      2018-12-25 00:00:00
1         2018-12-25 00:00:00      2018-12-25 09:00:00

Any help is much appreciated.

Ömer Erden
  • 7,680
  • 5
  • 36
  • 45
Andrew
  • 43
  • 4
  • 1
    Please explain the logic. – Gordon Linoff Jul 16 '19 at 01:09
  • From the above, I take it you mean when a start and end time have different days, you wish to adjust them into multiple records, one for each day (setting the times to midnight for the break). Would a three day record (start 2018-12-24 end 2018-12-26 be equal to 3 records, or still two?) Are you wanting to update the records in your database, or is this just a select query you're after? – Sam Jul 16 '19 at 01:58
  • @Sam - if period spans across 3 dates - it should be 3 records created from one and yes - I'm looking for a simple select here. – Andrew Jul 16 '19 at 06:26
  • Your problem is solvable using a number generator. Here are some example solutions (it is not same as your question, but should give you an idea): https://stackoverflow.com/a/53458694/2469308 and https://stackoverflow.com/a/53418090/2469308 – Madhur Bhaiya Jul 16 '19 at 11:53
  • thanks everyone for your input. I was able to find out a simple solution on my own. topic can be closed now. – Andrew Jul 16 '19 at 19:12

1 Answers1

0

Try to initiate your sql code like this. It's a long shot but I hope it get you started somewhere.

select tx.col1
FROM
(  (
      SELECT start_time as col1 from  t1
  )
  UNION 
  (
      SELECT end_time as col1 from  t1
   )
) as tx
dodzb
  • 379
  • 2
  • 5