CODEWITHSUNDEEP
phpmysqldatetime
spinozf
spinozf

Reputation: 67

MySQL Date comparison advice

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;
        }

Upvotes: 1

Views: 268

Answers (3)

Jeff Ferland
Jeff Ferland

Reputation: 18312

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.

Upvotes: 0

MBCook
MBCook

Reputation: 14504

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.

Upvotes: 0

ajreal
ajreal

Reputation: 47321

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

Upvotes: 4

Related Questions