Re: SQL Query Question

[Michael Stassen]

If you do any math on your column, no index on the column can be used.  If 
possible, you should always try to write your condition so that the 
calculations are done on the value(s) to compare to, not on the column.  So, 
assuming you have no rows with future timestamps, something like this should 
work:

  SELECT * FROM yourtable
  WHERE queue_time BETWEEN CURDATE() AND NOW();
If you might have timestamps later than NOW for some reason, you could 
change this to something like:

  SELECT * FROM yourtable
  WHERE queue_time BETWEEN CURDATE()
  AND (CURDATE + INTERVAL 1 DAY - INTERVAL 1 SECOND);
To select yesterday's rows, you could do something like:

  SELECT * FROM yourtable
  WHERE queue_time BETWEEN (CURDATE() - INTERVAL 1 DAY)
  AND (CURDATE() - INTERVAL 1 SECOND);
To help you visualize what's happening here, try

  SELECT CURDATE(), NOW(),
  CURDATE() + INTERVAL 1 DAY - INTERVAL 1 SECOND,
  CURDATE() - INTERVAL 1 DAY,
  CURDATE() - INTERVAL 1 SECOND;
You say you've been wrestling with the docs, so you probably already seen 
these, but just in case:

 <http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html>
 <http://dev.mysql.com/doc/mysql/en/Date_and_time_types.html>
Michael

Victor Pendleton wrote:

The you will need to use the second format.
DATE_FORMAT(queue_time, '%Y%m%d') = CURRENT_DATE()
-----Original Message-----
From: Dirk Bremer (NISC)
----- Original Message ----- 
From: "Victor Pendleton" <[EMAIL PROTECTED]>

If your data is stored in the following format
2004-04-16 00:00:00
you can do WHERE queue_time = CURRENT_DATE() + 0
You will also be able to take advantage of an index.
....
Else, if you data is kept in the datetime format,
2004-04-16 15:53:27
one option is to do
WHERE DATE_FORMAT(queue_time, '%Y%m%d') = CURRENT_DATE() + 0
...no index usage though
Victor,

The data defined as a timestamp, i.e. a number rather than a string, so
it
has YYYYMMDDHHMMSS values. So it looks like I'll need to do some type of
substring on it.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]