"Joachim Durchholz" <jo@durchholz.org> wrote news:f1k6bk$vkv$1@online.de...

>> select prova,risoluzione, inizio, timediff(risoluzione,inizio) as
>> delta
>> from statistiche where (prova=1 and risoluzione is not null) order by
>> diff asc

> It seems to be in lexicographic order ('01:31:41' < '115:25:05' <
> '75:09:15').
>
> Let me guess:
> You subtract two date/time values to get a diff.
> Behind the scenes, the result is some duration type, which gets converted
> to a string when storing in the 'diff' column.
> Mysql then sorts strings since it doesn't know that the values are really
> durations.

The MySQL manuel reads as following:
TIMEDIFF() returns expr1 - expr2 expressed as a time value.

So the function timediff does return a time value not a string. I would
assume that sorting would work fine as Giovanni did expect it. But maybe I'm
overlooking something here.


Nevertheless, you can work around it pretty easy by sorting by seconds:

mysql> SELECT risoluzione, inizio, timediff(risoluzione,inizio) AS delta
-> FROM statistiche
-> WHERE risoluzione IS NOT NULL
-> ORDER BY TIME_TO_SEC(delta) ASC;
+---------------------+---------------------+-----------+
| risoluzione | inizio | delta |
+---------------------+---------------------+-----------+
| 2007-04-29 15:16:55 | 2007-04-29 15:15:55 | 00:01:00 |
| 2007-05-02 23:25:04 | 2007-05-02 23:23:57 | 00:01:07 |
| 2007-05-05 20:28:21 | 2007-05-05 20:26:12 | 00:02:09 |
| 2007-05-05 10:21:08 | 2007-05-05 10:18:47 | 00:02:21 |
| 2007-04-30 16:52:50 | 2007-04-30 16:45:37 | 00:07:13 |
| 2007-05-05 14:23:42 | 2007-05-05 13:40:07 | 00:43:35 |
| 2007-04-29 23:58:17 | 2007-04-29 22:26:36 | 01:31:41 |
| 2007-05-04 12:07:43 | 2007-05-01 08:58:28 | 75:09:15 |
| 2007-05-04 23:43:53 | 2007-05-01 14:11:14 | 81:32:39 |
| 2007-05-05 16:35:21 | 2007-04-30 21:10:16 | 115:25:05 |
+---------------------+---------------------+-----------+

I used the TIME_TO_SEC() function to convert the time diffs to seconds. I
don't know if there are any performace impacts to expect by doing so.
--
Matthias Wirtz - Karlsruhe, DE