NB! ...WHERE doycol=312
Valid only for dates <= 28.Feb
OR Dates > 29.Feb but not in leap year(s) ;-)
> doycol=DAYOFYEAR("2004-11-07")
>
> or
>
> doycol=312
Eric McGrane wrote:
How about adding another column that stores the day of year for the
birthday. You could then index on this column and your
From: "Brent Baisley"
> You don't have to replicate the data, and you shouldn't. Since you will
> be searching on parts of the data, you may want to store it in parts.
> Then you can create indexes to "merge" the data for searching.
I'll be searching both on parts of the date as well as on the ent
Store month*100+day
1999-03-01 --> 301
you can also say "it's your birthday today" when 02/29 doesn't exists!
Santino
At 13:05 -0500 18-11-2004, [EMAIL PROTECTED] wrote:
Good idea! But, that method will fail for dates past Feb 29th on leap
years.
MYSQL>select dayofyear('1999-03-01'), Dayofyear('2
Yup, very good point. I am the one who is sorry. My bad.
E
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 18, 2004 1:06 PM
To: McGrane, Eric
Cc: [EMAIL PROTECTED]
Subject: Re: efficient query for: "it's your birt
Good idea! But, that method will fail for dates past Feb 29th on leap
years.
MYSQL>select dayofyear('1999-03-01'), Dayofyear('2000-03-01');
+-+-+
| dayofyear('1999-03-01') | Dayofyear('2000-03-01') |
+-+-
How about adding another column that stores the day of year for the
birthday. You could then index on this column and your query would be for
11/7 would be
doycol=DAYOFYEAR("2004-11-07")
or
doycol=312
E
""Jigal van Hemert"" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> I have d
You don't have to replicate the data, and you shouldn't. Since you will
be searching on parts of the data, you may want to store it in parts.
Then you can create indexes to "merge" the data for searching.
For instance, you use three fields for storage: year, month, day. The
you can create two co
From: "Rhino" <[EMAIL PROTECTED]>
> I don't know if it is more efficient but I would write this query as
> follows, simply because it is easier to understand what it is doing when
you
> look at it:
>
> select [whatever columns you want]
> from mytable
> where month(birthdate) = 11
> and dayofmonth(
[snip]
From: "Jay Blanchard" <[EMAIL PROTECTED]>
> So if I index a date field (given -mm-dd) and then
>
> SELECT `userNames`
> FROM `userTable`
> WHERE SUBSTRING(`userBirthDate`, 6, 5) = SUBSTRING(NOW(), 6, 5)
>
> it is still very fast. I have a table with several thousand records in
> it and I
From: "Jay Blanchard" <[EMAIL PROTECTED]>
> So if I index a date field (given -mm-dd) and then
>
> SELECT `userNames`
> FROM `userTable`
> WHERE SUBSTRING(`userBirthDate`, 6, 5) = SUBSTRING(NOW(), 6, 5)
>
> it is still very fast. I have a table with several thousand records in
> it and I get ba
From: "Brent Baisley" <[EMAIL PROTECTED]>
> Without breaking the "date" up into it's separate parts, you can't use
> an index, so you will always do a full table scan. Your searches will
> get slower as you add more records.
That was what I feared; I was just hoping that MySQL wouldn't treat the
- Original Message -
From: "Jigal van Hemert" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, November 18, 2004 4:28 AM
Subject: efficient query for: "it's your birthday today"
> I have date of birth stored in a DATETIME column and ne
[snip]
Really what you are trying to do is search on month + day, not a date.
For special "dates" (birthday, anniversary, etc), I always store the
year separately. Especially since some people don't really want you to
know how old they are.
Without breaking the "date" up into it's separate part
Really what you are trying to do is search on month + day, not a date.
For special "dates" (birthday, anniversary, etc), I always store the
year separately. Especially since some people don't really want you to
know how old they are.
Without breaking the "date" up into it's separate parts, you
I have date of birth stored in a DATETIME column and need to find for which
persons a certain date is their birthday.
I've tried so far:
DATE_FORMAT (col, '%m%d') = '1107'=> 0.2001 sec
col LIKE '%-11-07%'=> 0.1643 sec
col RLIKE '-11-07'=> 0.1702 sec
Are ther
15 matches
Mail list logo
