Wouldnt that only work for the current year? For example I was born on
1970-08-25, "select id from members where birthday = now();" wouldnt
return my birthday if it was today. Or am I missing something new in
5.0.11?
Gleb Paharenko wrote:
Hello.
Why don't you want to use just:
select id from members where birthday = now();
See:
mysql> show create table members\G;
*************************** 1. row ***************************
Table: members
Create Table: CREATE TABLE `members` (
`id` int(11) NOT NULL auto_increment,
`birthdate` date default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select * from members;
+----+------------+
| id | birthdate |
+----+------------+
| 1 | 2004-02-29 |
| 2 | 2005-02-28 |
| 3 | 2005-08-24 |
+----+------------+
mysql> select id from members where birthdate = now();
+----+
| id |
+----+
| 3 |
+----+
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2005-08-24 20:27:20 |
+---------------------+
1 row in set (0.00 sec)
mysql> select version();
+-----------------------+
| version() |
+-----------------------+
| 5.0.11-beta-debug-log |
+-----------------------+
1 row in set (0.00 sec)
If you add an index on birthdate, you can avoid table scans.
create index `birthdate` on members(birthdate);
mysql> explain select id from members where birthdate = now()\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: members
type: ref
possible_keys: birthdate
key: birthdate
key_len: 4
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
Pooly <[EMAIL PROTECTED]> wrote:
Hi,
I would like to display a list of members who have their birthday a
given day (today for instance).
My idea is to store their birth date in a column, and then query the
table against the column. But the query would be like :
select id from members where MONTH(birthday) =3D MONTH(NOW()) AND
DAY(birthday)=3DDAY(NOW())
but it would perform a entire table scan with that.
What would be your best strategy for that sort of query ?
And how would you deal with 29th of february ?
--=20
Pooly
Webzine Rock : http://www.w-fenec.org/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
