As soon as the new month rolled over, it happened again. The problem went away sometime in mid-May. The only difference between the current problems and the old is that we're now running 3.23.38. Any ideas? Chris ---------------------------------------------------------------------- Forwarded message: > From [EMAIL PROTECTED] Tue May 1 14:37:53 2001 > Date: Tue, 1 May 2001 14:37:53 -0500 > From: "Christopher P. Lindsey" <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Subject: Dates not compared properly? > Message-ID: <[EMAIL PROTECTED]> > Mime-Version: 1.0 > Content-Type: text/plain; charset=us-ascii > X-Mailer: Mutt 1.0.1i > Content-Length: 2290 > Lines: 63 > > 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
