Oracle中如何从Timestamp类型字段筛选特定日期？

问题描述 #

我需要编写Oracle查询语句，获取特定日期售出的平板电脑数据，涉及的order_date字段为Timestamp类型。以下是我编写的查询语句：

SELECT 
  Order_detail.order_detail_id,
  product_name AS product,
  Product_categ_type.product_categ_type AS category,
  Order_line.qty AS qty,
  order_date
FROM Product, Order_detail, Order_line, Product_categ_type
WHERE Order_detail.order_detail_id = Order_line.order_detail_id
  AND Product.product_id = Order_line.product_id
  AND Product.product_categ_type_id = 3
  AND Product_categ_type.product_categ_type LIKE 'TB%'
  AND order_date = TO_DATE('2022-12-02' ,'yyyy,mm,dd')

该语句可正常执行但无数据返回，我认为问题出在日期筛选的方式上。同时附上相关表的插入语句：

-- INSERT INTO PRODUCT TABLE
INSERT INTO Product (product_id,product_categ_type_id,product_name,price,stock_qty) VALUES (174,1,'iWatch',454,183);

INSERT INTO Product (product_id,product_categ_type_id,product_name,price,stock_qty) VALUES (194,3,'Samsung Galaxy Tab',398,114);

INSERT INTO Product (product_id,product_categ_type_id,product_name,price,stock_qty) VALUES (139,1,'Fitness Tracker',312,122);

INSERT INTO Product (product_id,product_categ_type_id,product_name,price,stock_qty) VALUES (150,3,'iPad',366,189);

-- INSERT INTO PRODUCT CATEGORY TABLE

INSERT INTO Product_categ_type (product_categ_type_id,product_categ_type) VALUES (1,'AC');
INSERT INTO Product_categ_type (product_categ_type_id,product_categ_type) VALUES (2,'SP');
INSERT INTO Product_categ_type (product_categ_type_id,product_categ_type) VALUES (3,'TB');

-- INSERT INTO Order detail

INSERT INTO Order_detail (order_detail_id,product_id,customer_id,emp_id) VALUES (144,150,196,113);
INSERT INTO Order_detail (order_detail_id,product_id,customer_id,emp_id) VALUES (183,139,140,197);
INSERT INTO Order_detail (order_detail_id,product_id,customer_id,emp_id) VALUES (172,194,185,113);

-- INSERT INTO Order line

INSERT INTO Order_line (product_id,order_detail_id,qty) VALUES (150,144,1);
INSERT INTO Order_line (product_id,order_detail_id,qty) VALUES (139,183,5);
INSERT INTO Order_line (product_id,order_detail_id,qty) VALUES (194,172,1);

问题分析与修正 #

1. 日期筛选逻辑错误 #

order_date是Timestamp类型，包含时分秒信息。用TO_DATE('2022-12-02','yyyy,mm,dd')得到的是日期类型（时分秒默认为00:00:00），只有当order_date的时分秒恰好全为0时才会匹配，实际场景中几乎不可能。正确的做法有两种：

• 方法一：截断Timestamp的时分秒

  AND TRUNC(order_date) = TO_DATE('2022-12-02','yyyy-mm-dd')
  

  注意：原格式字符串的分隔符是逗号，需改成与输入日期匹配的横杠yyyy-mm-dd，避免格式不兼容问题。
• 方法二：范围查询（推荐，可利用索引）

  AND order_date >= TO_TIMESTAMP('2022-12-02 00:00:00','yyyy-mm-dd hh24:mi:ss')
  AND order_date < TO_TIMESTAMP('2022-12-03 00:00:00','yyyy-mm-dd hh24:mi:ss')
  

2. 表连接缺失关联条件 #

原查询中Product_categ_type表未与其他表关联，会产生笛卡尔积，导致数据异常或无有效结果。需添加关联条件：Product.product_categ_type_id = Product_categ_type.product_categ_type_id。

3. 插入数据缺失order_date值 #

你提供的Order_detail插入语句未包含order_date字段，导致该字段值为NULL，无法匹配日期条件。需补充插入order_date值，示例：

INSERT INTO Order_detail (order_detail_id,product_id,customer_id,emp_id,order_date) 
VALUES (144,150,196,113,TO_TIMESTAMP('2022-12-02 10:30:00','yyyy-mm-dd hh24:mi:ss'));

修正后的完整查询语句 #

SELECT 
  od.order_detail_id,
  p.product_name AS product,
  pct.product_categ_type AS category,
  ol.qty AS qty,
  od.order_date
FROM Product p
JOIN Order_line ol ON p.product_id = ol.product_id
JOIN Order_detail od ON ol.order_detail_id = od.order_detail_id
JOIN Product_categ_type pct ON p.product_categ_type_id = pct.product_categ_type_id
WHERE p.product_categ_type_id = 3
  AND pct.product_categ_type LIKE 'TB%'
  AND od.order_date >= TO_TIMESTAMP('2022-12-02 00:00:00','yyyy-mm-dd hh24:mi:ss')
  AND od.order_date < TO_TIMESTAMP('2022-12-03 00:00:00','yyyy-mm-dd hh24:mi:ss');

这里使用ANSI JOIN语法，逻辑更清晰，避免笛卡尔积问题。

内容的提问来源于stack exchange，提问作者sillyconAdmin