Felix Geerinckx wrote:
> The OP had contradictory requirements imho.
agreed ;)
> Your solution:
>
> > SELECT name, birthdate,
> @years:=(YEAR(birthdate) - YEAR(CURRENT_DATE()))
> > FROM Hugo
> > WHERE WEEK(birthdate) =
> WEEK(DATE_ADD(CURRENT_DATE(), INTERVAL @years
> YEAR));
>
> viotates the following rule from
>
http://dev.mysql.com/doc/mysql/en/variables.html
>
> "The general rule is to never assign a value to a
> user variable in one part of a
> statement and use the same variable in some other
> part the same statement.
> You might get the results you expect, but this is
> not guaranteed."
>
> In this particular case, I believe the WHERE is
> executed before the assignment of
> the user variable. E.g.
>
> CREATE TABLE foo (id INT NOT NULL);
> INSERT INTO foo VALUES (1);
> SET @a := NULL;
> SELECT id, @a:=id FROM foo WHERE @a = 1;
>
> (unexpectedly?) produces an empty set.
>
> But even when you put the full expression in the
> WHERE clause, you don't always
> get correct results:
>
> I think we can agree (according to the OP's
> requirements) that someone born on
> 1962-08-03 should show up in the results when run
> with a CURRENT_DATE
> of 2005-08-05. This is not the case, however
I did not interpret the OP's problem as such. I interpreted it to mean "regardless of which day of the week I run my script, I want the exact same names to be returned." If that's the case, then, no, I don't think you could assume that with a CURRENT_DATE of '2005-08-05' that someone with a birthday of '1962-08-03' would be returned. It would depend entirely on which week of the year that '1962-08-03' fell within. Without using the uearweek, the interval at which the CRON script is checking is fluid, instead of static. However, you raise an interesting code sample... cont'd below.
>
> By the way
>
> SELECT CURRENT_DATE + 7 - INTERVAL
> DAYOFWEEK(CURRENT_DATE) DAY;
>
> always produces the last day of a week
I like this little trick! Pretty cool. :)
Although, when we substitute the above into your original SQL, we get:
mysql> SELECT CURRENT_DATE + 7 - INTERVAL DAYOFWEEK(CURRENT_DATE) DAY;
+---------------------------------------------------------+
| CURRENT_DATE + 7 - INTERVAL DAYOFWEEK(CURRENT_DATE) DAY |
+---------------------------------------------------------+
| 2005-08-13 |
+---------------------------------------------------------+
1 row in set (0.11 sec)
mysql> SELECT
-> name, birthdate
-> FROM Hugo
-> WHERE
-> YEAR(FROM_DAYS(TO_DAYS(CURRENT_DATE + 7 - INTERVAL DAYOFWEEK(CURRENT_DATE) DAY)-TO_DAYS(birthdate)+1)) =
-> YEAR(FROM_DAYS(TO_DAYS(CURRENT_DATE + 7 - INTERVAL DAYOFWEEK(CURRENT_DATE) DAY)-TO_DAYS(ADDDATE(birthdate, INTERVAL 7 DAY))+1))+1;
+-------+------------+
| name | birthdate |
+-------+------------+
| Jason | 1962-08-08 |
| Josh | 1962-08-09 |
| Steve | 1962-08-10 |
| Bob | 1962-08-11 |
| Brian | 1962-08-12 |
+-------+------------+
5 rows in set (0.58 sec)
which has different results from my code because of the assumption about what consitutes a week...
Interesting stuff which brings up a number of thoughts about interpretation of user problem domains and date/time issues. Thanks for taking the time to comment :)
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com