MySQL Date comparison advice

Question

I'm setting up a script to run daily and check for members who meet a certain age - automated emails can be set up in a CMS and assigned to be sent at any age, either in months or years. To handle this via PHP and MySQL, the number of months is passed as a parameter to a method, which I deal with as below. However, I'm not sure I'm going about this in the easiest way! Partly because of the formatting of the UK date format, I'm converting from string to datetime to unix timestamp to make the comparison. Can anyone find a better way of going about this? Thanks

        // If num of months provided is a year, make calculation based on exact year
        if ($age_in_months % 12 == 0)
        {
            // Using 365 days here (60 * 60 * 24 * 365 = 3153600)
            $clause = 'WHERE ROUND((UNIX_TIMESTAMP() - UNIX_TIMESTAMP(STR_TO_DATE(dob, "%d/%m/%Y"))) / 31536000) = ' . $age_in_months;
        }
        else 
        {
            // Using 30 days as avg month length (60 * 60 * 24 = 86400) - convert months to days
            $clause = 'WHERE ROUND((UNIX_TIMESTAMP() - UNIX_TIMESTAMP(STR_TO_DATE(dob, "%d/%m/%Y"))) / 86400) = ' . $age_in_months * 30;
        }

Don't be a fool, store date in a proper format. That's what you have to do first. — Your Common Sense, Dec 20 '10 at 20:15
See comment below, the date storage format I'm stuck with here — spinozf, Dec 22 '10 at 09:55

score 4 · Answer 1 · answered Dec 20 '10 at 18:45

4

change that column to type date or datetime
don't use UK date format, use ISO-8601 format
and index on that column

answered Dec 20 '10 at 18:45

ajreal

46,720
11
89
119

Hi, I'm stuck with the UK format unfortunately as it's an existing database I need to work with - I normally would use UNIX timestamps for data storage. – spinozf Dec 20 '10 at 19:20

score 0 · Answer 2 · answered Dec 20 '10 at 20:08

I'd just use the MySQL date logic. You can be sneaky and use the fact that the DOB column is stored as text this lie this:

SELECT whatever FROM users WHERE dob LIKE DATE_FORMAT(CURDATE(), '%d/%m/%%');

This will take the current date, format it like a UK date (the %% turns into a single %). So for today (as I post), that would be '20/12/%'. It uses that for a LIKE on dob, giving you everyone with a birthday of '20/12/(something)'.

It's a little weird, but it actually takes advantage of having the DOB stored in a text format. I'm assuming an index on DOB, although you could get away without it if you don't have too many people.

For the second query, it looks like you're trying to do a 'People who were born 6 months ago' type thing. Is that right? You could so something like this:

SELECT whatever FROM users WHERE DATE_ADD(CURDATE(), INTERVAL -6 MONTH) = STR_TO_DATE(dob, '%d/%m/%Y');

It's not a pretty, and if you want people who were born 6, 18, 30... months ago it won't work. In that case, I'd actually go with something like what you have. It's not ideal, but it would more-or-less work and the results may be close enough for you.

score 0 · Answer 3 · answered Dec 20 '10 at 20:13

datediff(now(),dob) will give you the difference in days between two dates.

If you want to see whether somebody is at least 18, try if (date_sub(now(),18 years) > dob)

Everybody born in February? if (month(dob) = 2)

See http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

Edit: and since you're stuck with (for unfathomable reasons) a database showing a date that's not in date format, replace dob with your STR_TO_DATE(dob... section.

MySQL Date comparison advice

3 Answers3

Linked