MySQL日期比较建议 [英] MySQL Date comparison advice

问题描述

我正在设置一个脚本，每天运行一次，并检查是否符合特定年龄的成员-可以在CMS中设置自动电子邮件，并可以将其分配为任意年龄（数月或数年）。为了通过PHP和MySQL处理此问题，将月数作为参数传递给方法，我将按照以下方法进行处理。但是，我不确定我会以最简单的方式进行操作！部分是由于英国日期格式的格式，我将字符串从datetime转换为unix时间戳以进行比较。
有人能找到更好的方法吗？谢谢

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

推荐答案

• 将该列更改为日期或日期时间


• 不使用英国日期格式，请使用 ISO-8601 格式


• 并在该列上进行索引


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

这篇关于MySQL日期比较建议的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！