时间戳

首页 > 解决方案 > 如果日期相同，查询 BETWEEN 不起作用

php - 如果日期相同，查询 BETWEEN 不起作用

问题描述

我有一个 Oracle 查询，它可以使用“WHERE something BETWEEN FROM and TO”正常工作，如果从/到不一样，但当它们是时，它就不起作用。

我尝试在 PHP 上验证 from/to 是否相同，然后我更改查询，将 BETWEEN 替换为“WHERE something = date”，但它也不起作用。

这是我提到的带有 PHP 验证的查询：

<?php

$db   = "(DESCRIPTION=(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 191.238.210.61)(PORT = 1521)))(CONNECT_DATA=(SID=opera)))";
$conn = oci_connect("opera", "opera", $db);

if (!$conn):
#--- Si no hay conexión, muestra error y detiene la depuración
    $m = oci_error();
    echo $m['message'];
elseif ($desde == $hasta):
    $consulta = "
                            SELECT
                                CONFIRMATION_NO,
                                NATIONALITY_DESC,
                                GUEST_NAME,
                                ADULTS,
                                CHILDREN,
                                RATE_CODE,
                                TRACE_ON,
                                DEPT_ID,
                                SUM(ADULTS) OVER()
                                || ' ADULTOS Y '
                                || SUM(CHILDREN) OVER()
                                || ' NIÑOS' AS RESULT
                            FROM
                            ( SELECT
                                MAX(CONFIRMATION_NO) AS CONFIRMATION_NO,
                                NATIONALITY_DESC,
                                GUEST_FIRST_NAME
                                || '  '
                                || GUEST_NAME GUEST_NAME,
                                MAX(ADULTS) AS ADULTS,
                                MAX(CHILDREN) AS CHILDREN,
                                RATE_CODE,
                                MIN(TRACE_ON) AS TRACE_ON,
                                LISTAGG(GUEST_RSV_TRACES.DEPT_ID, ',') WITHIN GROUP (ORDER BY NULL) AS DEPT_ID
                            FROM
                                GUEST_RSV_TRACES,
                                NAME_RESERVATION
                            WHERE trace_on = '$desde' 
                            AND guest_rsv_traces.resv_name_id = name_reservation.resv_name_id 
                            AND rate_code like 'ALL%' 
                            AND resv_status not in ('CANCELLED') 
                            AND guest_rsv_traces.dept_id not in ('TRF','OD')
                            GROUP BY
                            NATIONALITY_DESC,
                            GUEST_FIRST_NAME,
                            GUEST_NAME,
                            RATE_CODE
                            )
                            ";
    $query    = oci_parse($conn, $consulta);
    oci_execute($query);
elseif ($desde != $hasta):
    $consulta = "
                            SELECT
                                CONFIRMATION_NO,
                                NATIONALITY_DESC,
                                GUEST_NAME,
                                ADULTS,
                                CHILDREN,
                                RATE_CODE,
                                TRACE_ON,
                                DEPT_ID,
                                SUM(ADULTS) OVER()
                                || ' ADULTOS Y '
                                || SUM(CHILDREN) OVER()
                                || ' NIÑOS' AS RESULT
                            FROM
                            ( SELECT
                                MAX(CONFIRMATION_NO) AS CONFIRMATION_NO,
                                NATIONALITY_DESC,
                                GUEST_FIRST_NAME
                                || '  '
                                || GUEST_NAME GUEST_NAME,
                                MAX(ADULTS) AS ADULTS,
                                MAX(CHILDREN) AS CHILDREN,
                                RATE_CODE,
                                MIN(TRACE_ON) AS TRACE_ON,
                                LISTAGG(GUEST_RSV_TRACES.DEPT_ID, ',') WITHIN GROUP (ORDER BY NULL) AS DEPT_ID
                            FROM
                                GUEST_RSV_TRACES,
                                NAME_RESERVATION
                            WHERE trace_on BETWEEN '$desde' and '$hasta'
                            AND guest_rsv_traces.resv_name_id = name_reservation.resv_name_id 
                            AND rate_code like 'ALL%' 
                            AND resv_status not in ('CANCELLED') 
                            AND guest_rsv_traces.dept_id not in ('TRF','OD')
                            GROUP BY
                            NATIONALITY_DESC,
                            GUEST_FIRST_NAME,
                            GUEST_NAME,
                            RATE_CODE
                            )
                            ";
    $query = oci_parse($conn, $consulta);
    oci_execute($query);
endif;
?>

输出示例：（使用 BETWEEN '05-SEP-19' 和 '10-SEP-19'）：

145580  EE.UU   JAMES LANTZKE   3   0   ALLIN5NMT   05/09/19    HK  6 ADULTOS Y 0 NIÑOS
167410  EE.UU   KATHARINE BLOOD 3   0   ALLIN5NMT   05/09/19    HK  6 ADULTOS Y 0 NIÑOS

概括：

  1. 如果我做一个（在'05-SEP-19'和'10-SEP-19'之间）它可以工作
  2. 如果我做一个（在'05-SEP-19'和'05-SEP-19'之间）不起作用
  3. 如果我执行 (trace_on = '05-SEP-19') 不起作用

我想知道如何使用 BETWEEN 查询同一天或获取搜索特定日期的结果的方法。

提前致谢！

标签： phporacle

解决方案

日期总是有以下组成部分：年、月、日、小时、分钟和秒。用户界面并不总是显示所有这些组件（但它们仍然始终存在）。

如果您的TRACE_ON列有日期2019-09-05T12:34:56并且您正在尝试查看它是否是，BETWEEN '05-SEP-19' AND '05-SEP-19'那么 Oracle 将（假设与NLS_DATE_FORMAT会话参数匹配）将字符串转换为午夜的日期并且2019-09-05T12:34:56 BETWEEN 2019-09-05T00:00:00 AND 2019-09-05T00:00:00为 false，因此不会返回该行。

将您的查询更改为：

SELECT
       -- ...
       TO_CHAR( TRACE_ON, 'YYYY-MM-DD HH24:MI:SS' ),
       -- ...
 FROM (
  SELECT
          -- ...
  FROM    GUEST_RSV_TRACES
          INNER JOIN NAME_RESERVATION
          ON ( guest_rsv_traces.resv_name_id = name_reservation.resv_name_id )
  WHERE   trace_on >= TO_DATE( '$desde', 'DD-MON-RR' )
  AND     trace_on <  TO_DATE( '$hasta', 'DD-MON-RR' ) + INTERVAL '1' DAY
  AND     -- ...
  GROUP BY
          -- ...
)

推荐阅读