datetime类型查询遇到的问题(DM8：达梦数据库)

祢真伟大

已于 2023-09-11 10:44:33 修改

阅读量2.2k

点赞数

分类专栏： DMSQL 数据库文章标签：数据库 sql

于 2023-03-15 15:14:13 首次发布

本文链接：https://blog.csdn.net/qq_37358909/article/details/129553872

版权

数据库同时被 2 个专栏收录

85 篇文章 16 订阅

订阅专栏

DMSQL

30 篇文章 2 订阅

订阅专栏

DM8：达梦数据库datetime类型查询遇到的问题

环境介绍
1 示例

环境介绍

版本测试存在此问题,
dm8.1-20211111_x86_rh6_64_ent_8.1.2.18_pack16
dm8.1-2-128-22.08.04-166351-20005-CTM
dm8.1-3-12-2023.05.24-191193-20040-ENT
问题为条件查询列为DATETIME类型，日期范围的若 >=、> 月初的1号或月末的最后一天就会出现问题，查询结果会包含前一天的数据内容
解决方法 1 DATETIME(6)类型不要加 WITH TIME ZONE
解决方法 2 将DATETIME类型改为TIMESTAMP类型；

1 示例

1.1 表结构+数据

CREATE TABLE "T5236"
(
"A" INT,
"B" DATETIME(6)WITH TIME ZONE,
"C" TIMESTAMP(6),
"D" VARCHAR(10)) STORAGE(ON "MAIN", CLUSTERBTR) ;
INSERT INTO T5236 VALUES(0129,'2023-01-29 17:01:01','2023-01-29 17:01:01','2023');
INSERT INTO T5236 VALUES(0130,'2023-01-30 18:01:01','2023-01-30 18:01:01','2023');
INSERT INTO T5236 VALUES(0131,'2023-01-31 17:01:01','2023-01-31 17:01:01','2023');
INSERT INTO T5236 VALUES(0131,'2023-01-31 18:01:01','2023-01-31 18:01:01','2023');
INSERT INTO T5236 VALUES(0201,'2023-02-01 01:01:01','2023-02-01 01:01:01','2023');
INSERT INTO T5236 VALUES(0201,'2023-02-01 17:01:01','2023-02-01 17:01:01','2023');
INSERT INTO T5236 VALUES(0201,'2023-02-01 18:01:01','2023-02-01 18:01:01','2023');
INSERT INTO T5236 VALUES(0202,'2023-02-02 01:01:01','2023-02-02 01:01:01','2023');
INSERT INTO T5236 VALUES(0202,'2023-02-02 01:01:01','2023-02-02 01:01:01','2023');
INSERT INTO T5236 VALUES(0203,'2023-02-03 01:01:01','2023-02-03 01:01:01','2023');
INSERT INTO T5236 VALUES(0227,'2023-02-27 01:01:01','2023-02-27 01:01:01','2023');
INSERT INTO T5236 VALUES(0228,'2023-02-28 01:01:01','2023-02-28 01:01:01','2023');
INSERT INTO T5236 VALUES(0228,'2023-02-28 13:01:01','2023-02-28 13:01:01','2023');
INSERT INTO T5236 VALUES(0301,'2023-03-01 05:01:01','2023-03-01 05:01:01','2023');
INSERT INTO T5236 VALUES(0302,'2023-03-02 08:01:01','2023-03-02 08:01:01','2023');

COMMIT;

1.2 查询语句条件为DATETIME类型的列（部分结果异常）

SELECT * FROM T5236 WHERE  B>='2023-02-01 00:00:00' AND B<='2023-03-10 23:59:59.999' ;
SELECT * FROM T5236 WHERE  B>='2023-02-02 01:01:01' AND B<='2023-03-10 23:59:59.999' ;
SELECT * FROM T5236 WHERE  B>='2023-02-03 01:01:01' AND B<='2023-03-10 23:59:59.999' ;
SELECT * FROM T5236 WHERE  B>='2023-02-28 01:01:01' AND B<='2023-03-10 23:59:59.999' ;
SELECT * FROM T5236 WHERE  B>='2023-03-01 01:01:01' AND B<='2023-03-10 23:59:59.999' ;
SELECT * FROM T5236 WHERE  B>='2023-03-02 01:01:01' AND B<='2023-03-10 23:59:59.999' ;

在这里插入图片描述

1.3 查询语句条件为TIMESTAMP类型的列（结果正常）

查询结果均符合预期

SELECT * FROM T5236 WHERE  C>='2023-02-01 00:00:00' AND C<='2023-03-10 23:59:59.999' ;
SELECT * FROM T5236 WHERE  C>='2023-02-02 01:01:01' AND C<='2023-03-10 23:59:59.999' ;
SELECT * FROM T5236 WHERE  C>='2023-02-03 01:01:01' AND C<='2023-03-10 23:59:59.999' ;
SELECT * FROM T5236 WHERE  C>='2023-02-28 01:01:01' AND C<='2023-03-10 23:59:59.999' ;
SELECT * FROM T5236 WHERE  C>='2023-03-01 01:01:01' AND C<='2023-03-10 23:59:59.999' ;
SELECT * FROM T5236 WHERE  C>='2023-03-02 01:01:01' AND C<='2023-03-10 23:59:59.999' ;