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