douzi1986
2015-06-18 09:30
浏览 194
已采纳

MySQL按时间顺序存储为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?

图片转代码服务由CSDN问答提供 功能建议

我有一个 varchar 类型字段,用于存储格式为 HH的字符串: MM:SS 01:25:59 (有时没有 HH 部分,例如 25:59 )。

我想根据这个时间得到降序的结果,为此我带了 [str_to_date()] [1] 函数,目前我正在使用 str_to_date($ field_value,'%l:%i:%s')DESC 来实现这种排序。

奇怪的是使用这种格式%l:%i:%s 所有以 MM:SS 格式包含此字段的帖子都是正确排序的,但 HH:MM:SS 中的那些是'n' t。

1 - 如果我有这些值:

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

2 - 它们排序为:

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

3 - 哪个错了,应该是:

 <代码> 01:57:47 \ N01:20:26 \ N48:00 \ N15:20 \ N11  :35 
   
 
 

正如您在( 2 )中看到的那样,只有 HH:MM:SS 格式的次数 未正确放置( DESC

如何正确排序?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • duandun2218 2015-06-18 09:53
    已采纳

    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

    点赞 打赏 评论
  • doumu9799 2015-06-18 09:44

    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

    点赞 打赏 评论
  • dougong5817 2015-06-18 10:49

    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/>";
                        }
                }
    
    点赞 打赏 评论

相关推荐 更多相似问题