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

Answer 1

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

Answer 2

我只是使用MySQL的日期逻辑。您可以ne笑,并使用DOB一栏作为案文储存这一事实,即:

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

这将按目前的日期进行,其形式与联合王国的日期相同(百分比为单一 %)。因此,今天(作为我的职位)将达到20/12/%。它利用这一方法,为每个人提供20/12/(有些)的出生日。

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.

就第二个问题而言,它想像你想再做一个6个月前出生的人。这项权利吗? 你可以这样说:

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

如果你想要的是6岁、18岁和30岁以前出生的人,那就没有了。在该案中,我实际上与你一样。它不理想,但会更加无情地工作,结果可能足够接近于你。

Answer 3

datediff(now(dob)>将给你两个日期之间的差额。

如果你想看到有些人是否至少是18岁,请上<代码>if(日期_sub(now)(18岁) > dob)

2月份出生的每一个人? <代码>(月度(千)=2)

见rel=“nofollow”http://dev.mysql.com/doc/refman/5.1/en/date-and-time-Functions.html。

Edit:并且由于你(出于不可原谅的理由)重.了一个数据库,显示日期不是按期格式,将狗替换为<代码>STR_TO_DATE(dob.。

友情链接