Mysql query result date time wise

user4328158 Published at Dev

user4328158

I tried like this This is my Query

SELECT sloat_day, sloat_start_time 
FROM time_table 
WHERE sloat_day BETWEEN '2014-12-01' AND '2014-12-07' 
ORDER BY sloat_day, sloat_start_time ASC

I got below output

sloat_day   sloat_start_time
2014-12-01  6.30
2014-12-01  7.30
2014-12-01  18.30
2014-12-01  19.30
2014-12-02  6.30
2014-12-02  7.30
2014-12-02  18.30
2014-12-02  19.30
2014-12-03  6.30
2014-12-03  7.30
2014-12-03  18.30
2014-12-03  19.30
2014-12-04  18.30
2014-12-04  19.30
2014-12-05  7.30
2014-12-05  18.30
2014-12-05  19.30
2014-12-06  19.30
2014-12-07  7.30
2014-12-07  8.30
2014-12-07  18.30
2014-12-07  19.30

I want query result like below what i want to change in query to got this output please help me

2014-12-01  6.30
2014-12-02  6.30
2014-12-03  6.30
2014-12-04  18.30
2014-12-05  7.30
2014-12-06  19.30
2014-12-07  7.30
2014-12-01  7.30
2014-12-02  7.30
2014-12-03  7.30
2014-12-04  19.30
2014-12-05  18.30
2014-12-07  8.30
2014-12-01  18.30
2014-12-02  18.30
2014-12-03  18.30
2014-12-05  19.30
2014-12-07  18.30

Saharsh Shah

Try this:

SELECT sloat_day, sloat_start_time
FROM (SELECT sloat_day, sloat_start_time, 
             IF(@day=@day:=sloat_day, @id:=@id+1, @id:=1) rank  
      FROM time_table, (SELECT @id:=0, @day:='') AS a 
      WHERE sloat_day BETWEEN '2014-12-01' AND '2014-12-07' 
      ORDER BY sloat_day, sloat_start_time 
    ) AS A 
ORDER BY rank, sloat_day, sloat_start_time

Check SQL FIDDLE DEMO:

OUTPUT

|         SLOAT_DAY | SLOAT_START_TIME |
|-------------------| -----------------|
| December, 01 2014 | 06:30:00         |
| December, 02 2014 | 06:30:00         |
| December, 03 2014 | 06:30:00         |
| December, 04 2014 | 18:30:00         |
| December, 05 2014 | 07:30:00         |
| December, 06 2014 | 19:30:00         |
| December, 07 2014 | 07:30:00         |
| December, 01 2014 | 07:30:00         |
| December, 02 2014 | 07:30:00         |
| December, 03 2014 | 07:30:00         |
| December, 04 2014 | 19:30:00         |
| December, 05 2014 | 18:30:00         |
| December, 07 2014 | 08:30:00         |        
| December, 01 2014 | 18:30:00         |
| December, 02 2014 | 18:30:00         |
| December, 03 2014 | 18:30:00         |
| December, 05 2014 | 19:30:00         |
| December, 07 2014 | 18:30:00         |
| December, 01 2014 | 19:30:00         |
| December, 02 2014 | 19:30:00         |
| December, 03 2014 | 19:30:00         |
| December, 07 2014 | 19:30:00         |

Collected from the Internet

Please contact [email protected] to delete if infringement.