CODEWITHSUNDEEP
mysql
vikas bhandari
vikas bhandari

Reputation: 488

Need suggestion in mysql for grouping the time difference between start time and end time

This is the query for calculating the hour_diff which i added in input

SELECT t.START_TIME, t.END_TIME, (TIME_TO_SEC(t.END_TIME) - TIME_TO_SEC(t.START_TIME))/(60*60) as hour_diff FROM table1 as t ;

INPUT

start time  --- end time ---- hour_diff
10:20:00        10:40:00      .33 hour
11:00:00        11:15:00      .25 hour
11:00:00        12:20:00      .33 hours
11:00:00        13:00:00       2  hours
14:00:00        17:00:00       3  hours
14:20:00        18:00:00       3.66 hours

OUTPUT I what that output should be categorized as

0th hour - 1st hour --> x1 rows
1st hour - 2nd hour ---> x2 rows

like this:

    0-1 ----> 3
    1-2 ----> 1
    2-3 ----> 1
    3-4 ----> 1
    4-5 ----> 0

Can someone suggest this I have start time and end time in my table, hours i now how to calculate that's why i included it in query but i don't know how to categorize it in hour basis.

Upvotes: 1

Views: 56

Answers (1)

nacho
nacho

Reputation: 5397

You could try grouping by hour_diff like this and counting the number of results:

SELECT truncate((TIME_TO_SEC(t.END_TIME) - TIME_TO_SEC(t.START_TIME))/(60*60),0) as hour_diff, count(*)
FROM table1 as t 
group by truncate((TIME_TO_SEC(t.END_TIME) - TIME_TO_SEC(t.START_TIME))/(60*60),0);

Upvotes: 2

Related Questions