CODEWITHSUNDEEP
phpmysql
revo
revo

Reputation: 48711

MySQL order by time stored as HH:MM:SS

I have a varchar typed field which stores strings in this format HH:MM:SS i.e. 01:25:59 (and sometimes without HH part e.g. 25:59).

I want to have a descending order of results based on this time and for that I came with [str_to_date()][1] function and currently I'm using str_to_date($field_value,'%l:%i:%s') DESC to achieve this kind of sorting.

The odd thing is by using this format %l:%i:%s all posts having this field in MM:SS format are ordered correctly but those in HH:MM:SS aren't.

1-So if I have these values:

11:35
15:20
48:00
01:57:47
01:20:26

2-They are sorted as:

48:00
01:20:26
15:20
11:35
01:57:47

3-Which is wrong and should be:

01:57:47
01:20:26
48:00
15:20
11:35

As you see in (2) only times in format of HH:MM:SS are not placed correctly (DESC)

How can I have the right sorting?

Upvotes: 0

Views: 635

Answers (3)

Hytool
Hytool

Reputation: 1368

wanna do something at application side ??? bit lengthy, but it works. 

            $tim_arr = array ('11:35', '15:20', '48:00', '01:57:47', '01:20:26');

            $new_arr = array();
            foreach ($tim_arr AS $tim){
            $tim_chk = $key = '' ; $ntim_arr =array();
            $tim_chk =  substr_count($tim, ":");
            $ntim_arr = explode(':',$tim);
                    if($tim_chk == 2){
                            $ntim = ( (int)$ntim_arr[0]*60 + (int)$ntim_arr[1] ).':'.$ntim_arr[2];
                            $key = ( (int)$ntim_arr[0]*60 + (int)$ntim_arr[1] );
                    }
                    else{
                            $ntim = $tim;
                            $key  = $ntim_arr[0];
                    }
                    $new_arr[$key] = $ntim ;
            }
            krsort($new_arr);
            foreach ($new_arr AS $tim)
            {
                    $ntim_arr = explode(':',$tim);
                    if((int)$ntim_arr[0] >= 60){
                            echo str_pad(floor($ntim_arr[0] /60),2,"0",STR_PAD_LEFT).":".
                                 str_pad($ntim_arr[0] %60,2,"0",STR_PAD_LEFT).":".$ntim_arr[1]."<br/>";       
                    }
                    else{
                            echo $tim."<br/>";
                    }
            }

Upvotes: 0

lpg
lpg

Reputation: 4937

What about this?

SELECT  * FROM tbl 
ORDER BY TIME_TO_SEC(IF(LENGTH(str_time)<6,CONCAT("00:",str_time),str_time)) DESC

fiddle demo: http://sqlfiddle.com/#!9/4b5da/3

Upvotes: 2

Suresh Kamrushi
Suresh Kamrushi

Reputation: 16086

This is your query:

SELECT IF(LENGTH( columnName ) >5, STR_TO_DATE(columnName, '%h:%i:%s'), STR_TO_DATE(columnName, '%i:%s')) as modDate 
FROM `tableName` WHERE 1 order by modDate desc

SQL Fiddle: http://sqlfiddle.com/#!9/b6a52/1

Upvotes: 1

Related Questions