How to split datetime 1 row into 2 or more rows based duration-mysql

How to split 1 row of datetime record into multiple rows by splitting duration based on datetime for every hour

Input:

tutor_id | login_time           | logout_time           | duration
12       |'2017-02-20 11:20:20' | '2017-02-20 12:10:00' | 00:50:20
13       |'2017-02-20 12:30:00' | '2017-02-20 14:10:00' | 01:40:00

Want to cut every hour by exact time by dividing duration

Output:

tutor_id | login_time           | logout_time           | duration
12       |'2017-02-20 11:20:20' | '2017-02-20 12:00:00' | 00:40:20
12       |'2017-02-20 12:00:00' | '2017-02-20 12:10:00' | 00:10:00
13       |'2017-02-20 12:30:00' | '2017-02-20 13:00:00' | 00:30:00
13       |'2017-02-20 13:00:00' | '2017-02-20 14:00:00' | 01:00:00
13       |'2017-02-20 14:00:00' | '2017-02-20 14:10:00' | 00:10:00

Answer

You should try this query

Select top 10  login_time, logout_time,  
CAST(DATEADD(MINUTE,DATEDIFF(MINUTE,login_time,logout_time),'1990-01-01 00:00:00.000') as time) 
from Table

Here i have assumed that login and logout time have same date. it will give difference in minutes.

select * from [login-info]

login-time  logout-time
2017-12-13 11:09:38.557 2017-12-13 11:19:38.557
2017-12-13 11:15:45.490 2017-12-13 11:19:45.490
2017-12-13 11:06:49.700 2017-12-13 11:19:49.700
2017-12-13 11:11:07.580 2017-12-13 11:20:07.580


Select [login-time] ,[logout-time], 
CAST(DATEADD(MINUTE,DATEDIFF(MINUTE,[login-time],[logout-time]),'1990-01-01 00:00:00.000') as time) MINUTEDIFF
from [login-info]

login-time  logout-time MINUTEDIFF
2017-12-13 11:09:38.557 2017-12-13 11:19:38.557 00:10:00.0000000
2017-12-13 11:15:45.490 2017-12-13 11:19:45.490 00:04:00.0000000
2017-12-13 11:06:49.700 2017-12-13 11:19:49.700 00:13:00.0000000
2017-12-13 11:11:07.580 2017-12-13 11:20:07.580 00:09:00.0000000