jim wrote:
<snip>
One solution would be to use a variable:
SELECT @latest:= MAX(dateCreated);
Here I am getting the errors:
mysql> SELECT @latest:= MAX(dateCreated); ERROR 1054: Unknown column 'dateCreated' in 'field list' mysql> SELECT @latest:= MAX(user.dateCreated); ERROR 1109: Unknown table 'user' in field list
This works: SELECT @latest:= MAX(dateCreated) FROM user;
Sorry, my fault. You have to tell mysql which table to look in, just like any other query. I thought "FROM user", but apparently didn't type it. Not very helpful. I'm glad you figured it out.
SELECT * FROM user WHERE validated LIKE 'N' AND dateCreated <= DATE_SUB(@latest, INTERVAL 10 DAY);
Sorry for my newness here, but what is this technique called? You are creating a variable called @latest. I'd like to look this up in the doc.
Sure. No need to apologize. They're called user variables <http://dev.mysql.com/doc/mysql/en/Variables.html> .
<snip>
it. Couldn't you use a timeserver to keep in sync?
That's absolutely right. I'm already using nntp to keep the clock in synch, but as that relies on several outside factors (nntp server, 'net connection) I was trying to cleverly come up with something more reliable. We get about 150 validated users / day, so I figured that latest registered user would be a reliable place to count backwards from, until signups drop off, and even then this algorithm would err on the side of NOT selecting. You saw the flaw in my logic, though. I'll have to come up with something better.
In my experience, computers do a decent job of keeping time, but tend to drift *slowly* away from correct time. Periodic checking with a timeserver allows correction of the drift. If you can't connect to the timeserver for a while, your system time should still be kept, but should be expected to drift a little. If you've chosen a reliable timeserver, I'd be surprised if your system time were ever wrong by more than a few seconds, even if you only get to talk to the timeserver once every other day. I'd be astounded if system time were off by a day or more. That would indicate a serious problem with your internal clock.
Ultimately, no matter what you choose for a query, it will only be as reliable as the data. If your system time is at least accurate to the day, both the CURDATE and MAX user variable queries should return the same thing (so long as someone validated today). If your system time can ever have the wrong date, I don't think any query will save you.
Thanks again for the help and I welcome any other suggestions.
Jim
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
