MySQL DateDiff在Where子句中? [英] MySQL DateDiff in Where clause?

问题描述

我们正在使用Yii Framework，并创建了一个搜索表单，其中包含以下DatePicker遗留代码:

We are using Yii Framework and have created a search form to include the following DatePicker widge:

<?php echo $form->labelEx($model, 'availability_start_date');?>
        <?php 
        Yii::import('zii.widgets.jui.CJuiDatePicker');
        $this->widget('zii.widgets.jui.CJuiDatePicker', array(
            'name'=>'stattDate',
            'options'=>array(
                'showAnim'=>'fold',
            ),
            'htmlOptions'=>array(
                'style'=>'height:20px;'
            )
        ));
        ?>

需要使用此小部件来搜索与上述小部件中指定的值相比，available_start_date +/- 2天具有有效期的用户.

This widget needs to be used to search for users who have an availability_start_date +/- 2 days from the value specified in the above widget.

我们的UserController具有以下联接逻辑:

Our UserController has the following join logic:

if ($search_frm['availability_start_date']){
                    $join .= ' LEFT JOIN user_availability_start_date usd on u.id = usd.id';
                    $where .= 'AND usd.availability_start_date >= '.$search_frm
                ['availability_start_date'];
                }

当前，WHERE子句中的逻辑仅要求availability_start_date大于或等于窗口小部件的值的匹配项.

Currently the logic in the WHERE clause just asks for a match where the availability_start_date is greater than or equal to the widget's value.

如何修改上面的WHERE子句以选择那些小部件的值与Availability_start_date值相差+/- 2天的记录?

How do I modify the above WHERE clause to select those records where the widget's value is +/- 2 days different than the availability_start_date value?

更新:我将where子句修改为:

UPDATE: I've revised the where clause to read as follows:

if ($search_frm['availability_start_date']){
                    $join .= ' LEFT JOIN user_availability_start_date usd on u.id = usd.id';
                    $where .= ' AND usd.availability_start_date >= DATE_ADD('.$search_frm.', 
                INTERVAL -2 DAY)
                    AND usd.availability_start_date <= DATE_ADD(' .$search_frm.', INTERVAL 2 DAY)';
                }

不幸的是，当我测试逻辑时，将返回不符合此条件的记录.表单或页面上不会引发任何错误.

Unfortunately, when I test the logic, records are returned that don't meet this criteria. No errors are thrown on the form or page.

推荐答案

DATEDIFF() returns a value in days. For +/- 2 days you will want to use the absolute value ABS() returned by DATEDIFF(), verify that it is <= 2.

$where .= "AND ABS(DATEDIFF(DATE(usd.availability_start_date), DATE('" . $search_frm['availability_start_date'] . "'))) <= 2";

我还将DATE()中的DATEDIFF()自变量包装为截断时间部分(如果存在的话).如果您的应用程序不需要它们，请删除它们.

I have also wrapped the arguments to DATEDIFF() in DATE() to truncate off the time portions if they are present. Remove those if they are unnecessary for your application.

这篇关于MySQL DateDiff在Where子句中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！