List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:October 18 2005 2:59pm
Subject:Re: query help
View as plain text  
grKumaran wrote:
 > Hello,
 >
 > CREATE TABLE t (
 > dt datetime
 > )
 >
 > Please assume this is the table structure and contains thousands of
 > records. And I want to list them only last 12 months (that mean last 1
 > year) records exactly.
 >
 > For that I tried using the following query, but it list sometimes 13 months
 > when the current date is in the middle of the month.
 >
 > SELECT * FROM t WHERE DATE_ADD(dt, INTERVAL 1 YEAR) >= NOW();
 >
 > I request you to help me.  And thanking you for the consideration.

First, don't do that.  Your WHERE clause calculates a value based on the 
contents of a row, then compares the calculated value to a constant.  An 
index on your dt column cannot be used to find matching rows in this case, 
so a full table scan is required.  Instead, you should rewrite your query to 
make a direct comparison of the dt column to a calculated constant.  The 
following query is equivalent to yours, in that it matches the same rows, 
but it can use the index on dt:

   SELECT * FROM t
   WHERE dt >= NOW - INTERVAL 1 YEAR;

Peter Brawley wrote:
 > R,
 >
 > Try...
 >
 > DATE_ADD( dt, INTERVAL
 >         IF(YEAR(NOW())%4>0 AND YEAR(NOW())%100=0,366,365)
 >         DAY ) >= NOW()

(This won't use an index either.)  You think it's a leapyear problem?  I 
suspect Shawn is right that the real problem is that the requirements are 
not as stated.  The poster says he wants "only last 12 months (that mean 
last 1 year)", but then states that running the query in the middle of a 
month gives the wrong results.  My guess is that he wants something other 
than precisely the last year's results.  Perhaps he wants a year ending on 
the last day of the current or previous month.  Perhaps the start date 
shouldn't be precisely 1 year ago today, but at the start of a month.

Consider:

SET @last_month_end = LAST_DAY(CURDATE() - INTERVAL 1 MONTH)
                                          + INTERVAL 1 DAY
                                          - INTERVAL 1 SECOND;

SET @cur_month_yr = LAST_DAY(CURDATE()) - INTERVAL 1 YEAR + INTERVAL 1 DAY;

SET @last_month_yr = LAST_DAY(CURDATE()- INTERVAL 1 MONTH)
                      - INTERVAL 1 YEAR + INTERVAL 1 DAY;

SELECT @last_month_yr, @cur_month_yr, @last_month_end;
+----------------+---------------+---------------------+
| @last_month_yr | @cur_month_yr | @last_month_end     |
+----------------+---------------+---------------------+
| 2004-10-01     | 2004-11-01    | 2005-09-30 23:59:59 |
+----------------+---------------+---------------------+
1 row in set (0.00 sec)

Now something like

   SELECT * FROM t
   WHERE dt BETWEEN @cur_month_yr AND NOW();

or

   SELECT * FROM t
   WHERE dt BETWEEN @last_month_yr AND @last_month_end;

or some similar combination may do what you want.

Michael

Thread
query helpgrKumaran18 Oct
  • Re: query helpPeter Brawley18 Oct
    • Re: query helpMichael Stassen18 Oct