Professional Web Applications Themes

strange result in query... - MySQL

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

  1. #1

    Default strange result in query...

    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

    Giovanni Guest

  2. #2

    Default Re: strange result in query...

    sorry... replace la diff with delta... :)

    Giovanni Guest

  3. #3

    Default Re: strange result in query...

    == Quote from Giovanni (com)'s article 

    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.
    lark Guest

  4. #4

    Default Re: strange result in query...

    Giovanni schrieb: 

    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
    Joachim Guest

  5. #5

    Default Re: strange result in query...

    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?

    Giovanni Guest

  6. #6

    Default Re: strange result in query...

    "Joachim Durchholz" <org> wrote news:f1k6bk$vkv$de...
     [/ref]
     

    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


    Matthias Guest

  7. #7

    Default Re: strange result in query...

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

    To make your query work, i had to change 
    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 Guest

  8. #8

    Default Re: strange result in query...

    "Giovanni" <com> wrote
    news:googlegroups.com...
     

    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 Guest

  9. #9

    Default Re: strange result in query...

    Matthias Wirtz wrote: 
    >
    > 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.[/ref]

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

    --
    lark -- net
    To reply to me directly, delete "despam".
    lark Guest

  10. #10

    Default Re: strange result in query...

    why not? :)

    Giovanni Guest

  11. #11

    Default Re: strange result in query...

    > mysql> SELECT risoluzione, inizio, timediff(risoluzione,inizio) AS delta 

    Hell! It's really strange... are 3020400 seconds...
    mmm.... with datediff should be ok...but I don't know if it works with
    my query...
    however i cannot explain to myself, why it order by clause works in
    alphanumeric mode
    with datetime type...
    Does exists datetimediff?
    I cannot try it now... i've not a console :(

    tnx you all for help, guys!

    Giovanni Guest

Similar Threads

  1. Strange Query Result
    By Mediahouse in forum Coldfusion Database Access
    Replies: 3
    Last Post: January 24th, 11:20 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139