----- 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
> 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....
The following produced the result set that I was interested in.
set @BegOfMonth = DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1 DAY);
set @Start = DATE_SUB(@BegOfMonth, INTERVAL 1 month);
set @End = DATE_SUB(@BegOfMonth, 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 >= @Start AND queue_time < @End
ORDER BY ident;
Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters
USA Central Time Zone
636-922-9158 ext. 8652 fax 636-447-4471
[EMAIL PROTECTED]
www.nisc.cc
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
