SEO

Hi people. I'm running this query:

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

but i get this result:
risoluzione inizio diff
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-05 16:35:21 2007-04-30 21:10:16 115:25:05 <------*
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

Why the * row, is not ordered?
tnx in advance...


ah..the engine is mysql 4.1

sorry... replace la diff with delta...

== Quote from Giovanni (blastingproton@gmail.com)'s article
> Hi people. I'm running this query:
> select prova,risoluzione, inizio, timediff(risoluzione,inizio) as
> delta
> from statistiche where (prova=1 and risoluzione is not null) order by
> diff asc
> but i get this result:
> risoluzione inizio diff
> 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-05 16:35:21 2007-04-30 21:10:16 115:25:05 <------*
> 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
> Why the * row, is not ordered?
> tnx in advance...
> ah..the engine is mysql 4.1

i'd run it like this:

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

give it a shot and let us know how it does.

Giovanni schrieb:
> select prova,risoluzione, inizio, timediff(risoluzione,inizio) as
> delta
> from statistiche where (prova=1 and risoluzione is not null) order by
> diff asc
>
> but i get this result:
> risoluzione inizio diff
> 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-05 16:35:21 2007-04-30 21:10:16 115:25:05 <------*
> 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
>
> Why the * row, is not ordered?

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.

You may want to take a look at the Data Types chapter of mysql; I'm
pretty sure that it does have a data type for representing durations,
and these should then sort just as you intended.

HTH
Jo

lark the query has the same result... Joachim is right! Is an
alphabetic order and not numerical....
i'm try to do this

select prova,risoluzione, inizio, timediff(risoluzione,inizio) as
delta
from statistiche
where (prova=1 and risoluzione is not null)
order by cast (delta as int) // or datetime but doesn't works...

any idea?

"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

> 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;

To make your query work, i had to change
> -> ORDER BY TIME_TO_SEC(delta) ASC;
in
ORDER BY TIME_TO_SEC(timediff(risoluzione,inizio)) ASC;
because mysql, told me that cannot find "delta" field...
boh?
anyway, do you know if delta could be too large to stay in SECOND TYPE
and so reset itself to 0?

I hope I explained clearly....
tnx again

"Giovanni" <blastingproton@gmail.com> wrote
news:1178536761.366580.89000@p77g2000hsh.googlegro ups.com...

> anyway, do you know if delta could be too large to stay in SECOND TYPE
> and so reset itself to 0?

You mean if there migth happen an error (overflow)?

I just did the following experiment:

mysql> DROP TABLE statistiche;
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> CREATE TABLE statistiche
-> (
-> risoluzione DATETIME,
-> inizio DATETIME
-> )
-> ENGINE = MyISAM;
Query OK, 0 rows affected (0.15 sec)

[adding your 10 rows into table]

mysql> INSERT INTO statistiche VALUES ('2007-05-04 23:43:53', '1980-05-04
00:43:53');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO statistiche VALUES ('2007-05-04 23:43:53', '0001-05-04
00:43:53');
Query OK, 1 row affected (0.00 sec)

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 |
| 2007-05-04 23:43:53 | 1980-05-04 00:43:53 | 838:59:59 |
| 2007-05-04 23:43:53 | 0001-05-04 00:43:53 | 838:59:59 |
+---------------------+---------------------+-----------+
12 rows in set, 4 warnings (0.00 sec)

So even the difference of over 2000 years did not cause any error. Looks
like you are on the save side when using this query.

But you might have noted that the TIMEDIFF function shows only values up to
838:59:59 which represent a time difference of almost 34 days and 23 hours.
Which is a much smaller time difference then the 2000 years I used to test
the query.
--
Matthias Wirtz - Karlsruhe, DE

Matthias Wirtz wrote:
> "Giovanni" <blastingproton@gmail.com> wrote
> news:1178536761.366580.89000@p77g2000hsh.googlegro ups.com...
>
>> anyway, do you know if delta could be too large to stay in SECOND TYPE
>> and so reset itself to 0?
>
> You mean if there migth happen an error (overflow)?
>
> I just did the following experiment:
>
> mysql> DROP TABLE statistiche;
> Query OK, 0 rows affected (0.03 sec)
>
> mysql>
> mysql> CREATE TABLE statistiche
> -> (
> -> risoluzione DATETIME,
> -> inizio DATETIME
> -> )
> -> ENGINE = MyISAM;
> Query OK, 0 rows affected (0.15 sec)
>
> [adding your 10 rows into table]
>
> mysql> INSERT INTO statistiche VALUES ('2007-05-04 23:43:53', '1980-05-04
> 00:43:53');
> Query OK, 1 row affected (0.01 sec)
>
> mysql> INSERT INTO statistiche VALUES ('2007-05-04 23:43:53', '0001-05-04
> 00:43:53');
> Query OK, 1 row affected (0.00 sec)
>
> 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 |
> | 2007-05-04 23:43:53 | 1980-05-04 00:43:53 | 838:59:59 |
> | 2007-05-04 23:43:53 | 0001-05-04 00:43:53 | 838:59:59 |
> +---------------------+---------------------+-----------+
> 12 rows in set, 4 warnings (0.00 sec)
>
> So even the difference of over 2000 years did not cause any error. Looks
> like you are on the save side when using this query.
>
> But you might have noted that the TIMEDIFF function shows only values up to
> 838:59:59 which represent a time difference of almost 34 days and 23 hours.
> Which is a much smaller time difference then the 2000 years I used to test
> the query.

interesting find! this may have to go in mysql's bugzilla as a bug.
would you like to do that?

--
lark -- hamzee@sbcdeglobalspam.net
To reply to me directly, delete "despam".

why not?