查询日期范围和特定时间范围？

Question

我有一个 DB2 数据库查询，我需要在日期和特定时间之间提取数据。我提出了下面的查询，它正在工作，但没有获取 20110510 和 20110512 之间当天的数据，在本例中为 20110511，该数据应该是一整天的数据。

日期和时间是用户的参数，因此会根据他们的需要而改变。如果可能的话，我还希望能够使用相同的查询提取同一天的数据，例如：

Date last modify >= 20110512 AND
Date last modify <= 20110512 AND
Time last modify >= 090000   AND
Time last modify <= 230000   AND

任何帮助都会提前非常感激。谢谢。

SELECT A1.CHCASN, A1.CHTRKN,
SUM(A2.CDPAKU) AS UNITS, A1.CHACWT, SUM(A2.CDPRC * A2.CDPAKU) AS COST, SUM(A3.STRPRC * A2.CDPAKU) AS RETAIL, A1.CHDLM, A1.CHTLM
FROM CHCART00 A1, CDCART00 A2, STSTYL00 A3
WHERE A1.CHCASN = A2.CDCASN
AND A2.CDSTYL = A3.STSTYL
AND A2.CDCOLR = A3.STCOLR
AND A2.CDSDIM = A3.STSDIM
AND A1.CHSTAT = '25'
AND (A1.CHDLM = 20110510 AND A1.CHTLM >= '200000' OR A1.CHDLM = 20110512 AND A1.CHTLM <= '092000') AND A1.CHROUT = 'UPSPA'
GROUP BY A1.CHCASN, A1.CHTRKN, A1.CHACWT, A1.CHDLM, A1.CHTLM
ORDER BY A1.CHDLM, A1.CHTLM

Answer 1

示例：-（

两个日期之间）

`SELECT A1.CHCASN, A1.CHTRKN, SUM(A2.CDPAKU) AS UNITS, A1.CHACWT,
    SUM(A2.CDPRC * A2.CDPAKU) AS COST, SUM(A3.STRPRC * A2.CDPAKU) AS RETAIL,     
    A1.CHDLM, A1.CHTLM FROM CHCART00 A1, CDCART00 A2, STSTYL00 A3  
WHERE A1.CHCASN = A2.CDCASN AND A2.CDSTYL = A3.STSTYL AND      
    A2.CDCOLR = A3.STCOLR     AND A2.CDSDIM = A3.STSDIM AND A1.CHSTAT = '25' AND
    (A1.CHDLM BETWEEN 20110510 AND 20110512)      
    AND A1.CHROUT = 'UPSPA' GROUP BY A1.CHCASN, A1.CHTRKN, A1.CHACWT,
    A1.CHDLM, A1.CHTLM  
ORDER BY A1.CHDLM, A1.CHTLM` 

（同一日期）

`SELECT A1.CHCASN, A1.CHTRKN, SUM(A2.CDPAKU) AS UNITS, A1.CHACWT,
    SUM(A2.CDPRC * A2.CDPAKU) AS COST, SUM(A3.STRPRC * A2.CDPAKU) AS RETAIL,     
    A1.CHDLM, A1.CHTLM FROM CHCART00 A1, CDCART00 A2, STSTYL00 A3  
WHERE A1.CHCASN = A2.CDCASN AND A2.CDSTYL = A3.STSTYL AND      
    A2.CDCOLR = A3.STCOLR     AND A2.CDSDIM = A3.STSDIM AND A1.CHSTAT = '25' AND
    (A1.CHDLM = 20110511)     
    AND A1.CHROUT = 'UPSPA' GROUP BY A1.CHCASN, A1.CHTRKN, A1.CHACWT,
    A1.CHDLM, A1.CHTLM  
ORDER BY A1.CHDLM, A1.CHTLM `

Answer 2

看起来您的 WHERE 子句中缺少一些括号

试试这个：-

SELECT A1.CHCASN, A1.CHTRKN, SUM(A2.CDPAKU) AS UNITS, A1.CHACWT, 
    SUM(A2.CDPRC * A2.CDPAKU) AS COST, SUM(A3.STRPRC * A2.CDPAKU) AS RETAIL,
    A1.CHDLM, A1.CHTLM FROM CHCART00 A1, CDCART00 A2, STSTYL00 A3 
WHERE A1.CHCASN = A2.CDCASN AND A2.CDSTYL = A3.STSTYL AND 
    A2.CDCOLR = A3.STCOLR AND A2.CDSDIM = A3.STSDIM AND A1.CHSTAT = '25' AND
    ((A1.CHDLM = 20110510 AND A1.CHTLM >= '200000') 
    OR
    (A1.CHDLM = 20110511)
    OR
    (A1.CHDLM = 20110512 AND A1.CHTLM <= '092000')) 
    AND A1.CHROUT = 'UPSPA' GROUP BY A1.CHCASN, A1.CHTRKN, A1.CHACWT, 
    A1.CHDLM, A1.CHTLM 
ORDER BY A1.CHDLM, A1.CHTLM 

Answer 3

一种解决方案是将日期和时间列组合成时间戳或类似的结构，这样更容易用于多天的范围搜索。使用什么数据类型并不重要；重要的是。只需确保它遵循某种 YYYYMMDDHHMMSS 格式并以相同的方式转换您的输入参数，以便它们可以与列数据进行比较。

假设：

• 表 CHCART00 中的列 CHDLM 为 INTEGER，以 YYYYMMDD 格式存储日期
• 表 CHCART00 中的列 CHTLM 列为 CHAR(6)，以 24 小时 HHMMSS 格式存储时间

• 输入参数指定的窗口是连续的，没有间隙例如“仅限营业时间”

  AND (BIGINT(A1.CHDLM) * 1000000 + INTEGER(A1.CHTLM))

  >= (BIGINT(20110510) * 1000000 + INTEGER('230000'))

  AND (BIGINT(A1.CHDLM) * 1000000 + INTEGER(A1.CHTLM))

  <= ( BIGINT( 20110512 ) * 1000000 + INTEGER('090000'))

Answer 4

拉取查询的一个简单方法是这样的：

SELECT
  num_executions,
  num_exec_with_metrics,
  SUBSTR(stmt_text,1,2000000) AS stmt_text
FROM TABLE (MON_GET_PKG_CACHE_STMT('d', null, null, -1)) AS tf
WHERE
  (VARCHAR_FORMAT(INSERT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') > '2016-01-27 07:00:00'
  AND VARCHAR_FORMAT(INSERT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') <= '2016-01-27 08:59:59')
ORDER BY INSERT_TIMESTAMP DESC;