如何进行此日期计算？

Question

我的表中有一个名为expiry_date的列。我使用以下查询返回此日期加上6个月：

SELECT DATE_ADD(expiry_date, INTERVAL 6 MONTH) AS expiry_date;

我想修改上面的内容，这样如果expiry_date加上6个月是过去的(在CURRENT_DATE之前)，那么根据需要再增加6个月，直到日期在未来。

如何使用MySQL完成此操作？

Answer 1

第一种方法：

SELECT 
   case when expiry_date > SYSDATE() then
       DATE_ADD(   expiry_date  , INTERVAL 6 MONTH) 
   else
       DATE_ADD(
          DATE_SUB( 
             SYSDATE(),   
             INTERVAL 
             ( DATEDIFF( SYSDATE(), expiry_date )  % ( 6 * 30) )  
             MONTH
          )
          , 
          INTERVAL 6 MONTH) 
   end
   AS expiry_date
FROM ...

未测试。

Answer 2

我已经用以下数据进行了测试：

Table: `test`
uid      expiry_date
1        2011-11-03
2        2011-01-20

代码：

SELECT 
    `uid`,
    `expiry_date`,
    PERIOD_DIFF(
        date_format(DATE_ADD(CURDATE(),INTERVAL 1 DAY),'%Y%m'),
        date_format(`expiry_date`,'%Y%m')
    ) as `dif`, /* Straight Month Difference */
    CEIL(PERIOD_DIFF(
        date_format(DATE_ADD(CURDATE(),INTERVAL 1 DAY),'%Y%m'),
        date_format(`expiry_date`,'%Y%m')
    )/6) AS `dif2`, /* How many blocks of 6 months, rounded up */
    (
        IF(
            ( /* If 1 block is > today, add the 6 months and finish */
            DATE_ADD(
                CURDATE(),
                INTERVAL 1 DAY
            ) < DATE_ADD(
                `tOuter`.`expiry_date`,
                INTERVAL 6 MONTH
            )
        ), /* this is with just 6 months added */
        DATE_ADD(
            `tOuter`.`expiry_date`,
                INTERVAL 6 MONTH
            ), /* this works out how many blocks of 6 months to add */
            DATE_ADD(
                `tOuter`.`expiry_date`,
            INTERVAL (
                6 * CEIL( /* round up number of months */
                    PERIOD_DIFF( /* get number of months */
                        date_format(
                            DATE_ADD(
                                CURDATE(),
                                INTERVAL 1 DAY
                            ),
                            '%Y%m'
                        ),
                        date_format(
                            `expiry_date`,
                            '%Y%m'
                        )
                    )/6 /* divide months by 6 to match question */
                )
            ) MONTH /* add the dynamically calculated interval */
        )
    )
) AS `expiry_date_calculated` FROM `test` AS `tOuter`

其结果是：

uid     expiry_date      dif         dif2       expired_date_calculated
1       2011-11-03       0           0          2012-05-03
2       3011-01-20       10          2          2012-01-20

这是此输入的所需输出吗？

性能说明：这是一个糟糕的性能，我邀请其他人建议一个更有效的例程。如果将CURDATE()的日期作为字符串传入，这可能会更好地编写为存储过程。

示例:使用2个字段，需要0.04秒才能产生上面所需的结果。

编辑：较小版本：

SELECT 
 `id`, `expiry_date`,
 (IF((DATE_ADD(CURDATE(),INTERVAL 1 DAY) < DATE_ADD(`expiry_date`,INTERVAL 6 MONTH)),
   DATE_ADD(`expiry_date`,INTERVAL 6 MONTH),
   DATE_ADD(`expiry_date`,
    INTERVAL (6 * CEIL(PERIOD_DIFF(
        date_format(DATE_ADD(CURDATE(),INTERVAL 1 DAY),'%Y%m'),
        date_format(`expiry_date`,'%Y%m')
    )/6)) MONTH
   )
 )
) AS `expiry_date_calculated`

Answer 3

您应该在expiry_date中添加X years and 6 months或X years and 12 months。X是他们year part off now()和expiry_date之间的差值。

select if(dayofyear(expiry_date)>=dayofyear(now()), 
 date_add(test, interval concat(year(now())-year(expiry_date), '-6') year_month), 
 date_add(test, interval concat(year(now())-year(expiry_date), '-12') year_month)) 
as expiry_date from <table>;

一个例子。expiry_date是2009-10-01

一年中的第几天(Expiry_date)是274

一年中的某天(now())是325

所以第一个条件是假的。concat(year(now())-year(expiry_date), '-12'给出2011-2009=2年零12个月(字符串为'2-12')，date_add('2009-10-01', interval '2-12' year_month中使用的是2011-10-01 +6个月= 2012-05-01。