At 12:57 -0500 9/27/04, Dirk Bremer (NISC) wrote:
----- Original Message -----
From: <[EMAIL PROTECTED]>
To: "Dirk Bremer (NISC)" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Monday, September 27, 2004 11:40
Subject: Re: Date Range
I see that you are on 4.0.18 so you can't use many of the new date
functions (4.1.1+) but has to be an easier way. Let's try this for a
"yesterday" query
set @dtEnd = CURDATE()
set @dtStart = DATE_SUB(@dtEnd, INTERVAL 1 DAY)
SELECT ident,
job_coop,
cycle,
lpad(status,10,' ') as status,
type,
file_size,
date_format(queue_time,'%Y-%m-%d %T') as queue_time,
file_time,
transfer_start,
transfer_end
FROM queue
WHERE queue_time >= @dtStart
AND queue_time < @dtEnd
To compute the date range for "two months ago". (if the current month is
September, this will return July's data)
set @dtBegOfMonth = DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1
DAY)
set @dtEnd = DATE_SUB(@dtBegOfMonth, INTERVAL 1 month)
set @dtStart = DATE_SUB(@dtEnd, INTERVAL 1 month)
Then, continue with the same query as above. These should be really fast
as you are comparing constant values against what should be indexed table
data. You also avoid string conversions, substring comparisons, and other
data type conversions this way. The Date information stays date
information....
Shawn,
Thanks for the advice. Unfortunately, your example produces the following
error. Looking at it, I can see nothing obvious that is causing the error.
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use near
'set @dtStart = DATE_SUB(@dtEnd, INTERVAL 1 DAY)
You need semicolons at the end of each statement.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
