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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
