Re: Date Range

[SGreen]

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....

Here is the section of the manual that covers all of the Date and Time 
functions:
http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html

I like this method because you only have to build your query once. The 
only thing you need to change are the formulas to generate your starting 
and ending times of your date ranges.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine




"Dirk Bremer \(NISC\)" <dirk.bremer@stripped> wrote on 09/27/2004 11:37:17 
AM:

> I'm using version 4.0.18 on Win2K. I have a table whose timestamp-type 
field
> I want to select on for an interval of the last month. Imagine that the
> table contains multiple months of entries. On the first day of each 
month, I
> want to select all of the rows whose timestamp fields occurred in the
> previous month. This table will more than likely accumulate several 
years of
> data, so I need to be concerned for month/year boundary conditions, etc.
> 
> Currently I use a query such as the following to get the previous day's
> rows:
> 
> 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 substring(queue_time,1,8) = cast(date_sub(curdate(), interval 1 
day)
> as unsigned)
> order by queue_time;
> 
> My first thought was to use something like this, except I'm getting a 
syntax
> error:
> 
> 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 (substring(queue_time,1,8) between cast(date_sub(curdate(), 
interval
> 31 day) as unsigned) and cast(date_sub(curdate(), interval 1 day))
> order by queue_time;
> 
> What would be the best way to get the previous month's rows and what 
would
> be the slickest way to do the same?
> 
> Dirk Bremer - Systems Programmer II - ESS/AMS  - NISC St. Peters
> USA Central Time Zone
> 636-922-9158 ext. 8652 fax [phone]
> 
> dirk.bremer@stripped
> www.nisc.cc
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>