CODEWITHSUNDEEP
mysqlsql
DJo
DJo

Reputation: 2177

split row in to multiple rows of a table

I have one table which having columns start time and stop time.

for example 

 Start time           Stop Time
 2014-01-01 23:43:00  2014-01-02 03:33:00

This i need to split in to 2014-01-01 23:43:00 - 2014-01-02 00:00:00 and 2014-01-02 00:00:00 - 2014-01-02 03:33:00 as two separate rows.

Upvotes: 1

Views: 1491

Answers (2)

Aziz Shaikh
Aziz Shaikh

Reputation: 16524

You can break the two column into individual rows and then use the CASE statement, like this:

select 
  CASE WHEN t.timetype=1 THEN mytime 
       WHEN t.timetype=2 THEN CONCAT(DATE(mytime), ' 00:00:00') 
  END as starttime,
  CASE WHEN t.timetype=1 THEN CONCAT(DATE_ADD(DATE(mytime), INTERVAL 1 DAY), ' 00:00:00')
       WHEN t.timetype=2 THEN mytime 
  END as stoptime
from (
  select starttime as mytime, 1 as timetype from record
  union
  select stoptime as mytime, 2 as timetype from record
) t

Working demo: http://sqlfiddle.com/#!2/7ef31/30

Here the table t will have 2 rows for each row of your Records table, one row will contain the starttime and the other row will contain stoptime. Each row of table t will also contain a type. Type 1 for starttime and type 2 for stoptime. Then the CASE statement will be used to generate your required data.

Upvotes: 1

StanislavL
StanislavL

Reputation: 57381

select 
  if (temp.row_n=1,t.start_time,t.stop_time) as time
from mytable t,
     (select 1 as row_n
      union
      select 2 as row_n) temp

Upvotes: 0

Related Questions