MySQL按时间顺序存储为HH:MM:SS

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

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

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

1 - 如果我有这些值:</ p>

  11:35 
15:20
48:00
01: 57:47
01:20:26
</ code> </ pre>

2 - 它们排序为:</ p>

  48:00

01:20:26
15:20
11:35
01:57:47
</ code> </ pre>

3 - 哪个错了,应该是:</ p>

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

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

如何正确排序?</ p>
</ div>

展开原文

原文

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?

duanca3415
duanca3415 这就是我想要的。
5 年多之前 回复
dou29106
dou29106 从语义上讲:如果字符串中没有设置HH,为什么该字符串出现在HH:MM:SS下面而不是上面?
5 年多之前 回复

3个回答



这个怎么样?</ p>

  SELECT * FROM tbl 
ORDER BY TIME_TO_SEC(IF (LENGTH(str_time)&lt; 6,CONCAT(“00:”,str_time),str_time))DESC
</ code> </ pre>

小提示演示: http://sqlfiddle.com/#!9/4b5da/3 </ p>
</ div>

展开原文

原文

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



这是您的查询:</ p>

  SELECT IF(LENGTH(columnName)&gt;  5,STR_TO_DATE(columnName,'%h:%i:%s'),STR_TO_DATE(columnName,'%i:%s'))为modDate 
FROMtableName WHERE 1 order by modDate desc
</ code> </ pre>

SQL小提琴: http://sqlfiddle.com/# !9 / b6a52 / 1 </ p>
</ div>

展开原文

原文

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

douquanqiao6788
douquanqiao6788 它的工作原理但问题是我无法修改select语句。
5 年多之前 回复



想在应用程序端执行某些操作??? 有点长,但它的工作原理。 </ p>

  $ 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]&gt; = 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] “&LT峰; br /&gt;” 中;

}
其他{
echo $ tim。“&lt; br /&gt;”;
}
}
</ </ code> </ pre>
</ div>

展开原文

原文

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/>";
                    }
            }

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐