Howdy all...
I've run into a strange date problem as of midnight, May 1, 2001 on my
i686 Linux machine running mySQL 3.23.37.
For some reason, queries can no longer compare dates properly:
mysql> select count(*) from hit where date > NOW()-1135560;
+----------+
| count(*) |
+----------+
| 2633 |
+----------+
1 row in set (2.05 sec)
mysql> select count(*) from hit where date > NOW()-1145560;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (2.05 sec)
mysql> select NOW()-1135560,NOW()-1145560;
+----------------+----------------+
| NOW()-1135560 | NOW()-1145560 |
+----------------+----------------+
| 20010500007384 | 20010499997384 |
+----------------+----------------+
1 row in set (0.00 sec)
mysql> describe hit;
+--------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| dig_id | int(10) unsigned | | MUL | 0 | |
| date | timestamp(14) | YES | | NULL | |
| ip | varchar(16) | YES | | NULL | |
| site | varchar(255) | YES | | NULL | |
+--------+------------------+------+-----+---------+-------+
NOW() is being expanded properly, yet for some reason the comparison
of hit.date with NOW()-n fails when n is larger than an abitrary number.
As it becomes later and later in the day, n becomes larger and larger
(so eventually it will reach 1970 and everything will work again).
I've optimized the tables, but don't really have any other good ideas.
For now, I'm just doing
select count(*) as hits, substring(date,1,8) as foo from hit where ip NOT like
"x.x.x.%" AND ip != "x.x.x.x" group by foo order by foo desc limit 10;
to get the count of most recent hits instead of the faster
select count(*) as hits, substring(date,1,8) as whee from hit where date > NOW() -
10000000 AND ip NOT like "64.5.98.%" AND ip != "141.142.22.95" group by whee order by
whee desc;
Any ideas?
Thanks,
Chris
P.S. For those who are curious, the database tracks views of plant photos
on my Web site in real-time, including sites that have inlined.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
