如何在oracle中获取两个时间戳之间的记录

Question

我在oracle中有一列TIMESTAMP(6(数据类型，值为2022-04-01 18:02:42，我想获取两个日期时间之间的所有记录。我试过了，但没有运气，

select * from table 
where column BETWEEN '2022-04-01 18:02:42' and '2022-11-03 19:28:57' -- no records

也试过了

select * 
from table 
where column BETWEEN to_date('2022-04-01','yyyy-mm-dd') 
and to_date('2022-11-03','yyyy-mm-dd') -- a non-numeric character was found where a numeric was expected

和，

select * 
from table 
where column BETWEEN to_timestamp('2022-04-01','yyyy-mm-dd') 
and to_timestamp('2022-11-03','yyyy-mm-dd') -- a non-numeric character was found where a numeric was expected.

我想在这个日期取唱片！

谢谢。

Answer 1

您需要使用to_DATE和所需的格式掩码将文本转换为DATE，以将时间戳列与输入的时间戳值进行比较。

SQL> CREATE TABLE t(A TIMESTAMP);
Table created.
SQL>
SQL> INSERT INTO t(A) VALUES(to_date('2022-04-10T15:39:00', 'YYYY-MM-DD"T"HH24:MI:SS'));
1 row created.
SQL> INSERT INTO t(A) VALUES(to_date('2022-05-01T15:39:00', 'YYYY-MM-DD"T"HH24:MI:SS'));
1 row created.
SQL> INSERT INTO t(A) VALUES(to_date('2022-03-01T15:39:00', 'YYYY-MM-DD"T"HH24:MI:SS'));
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM t;
A
----------------------------
10-APR-22 03.39.00.000000 PM
01-MAY-22 03.39.00.000000 PM
01-MAR-22 03.39.00.000000 PM
SELECT *
FROM t
WHERE A BETWEEN
to_date('2015-04-06T15:39:00', 'YYYY-MM-DD"T"HH24:MI:SS')
AND
to_date('2022-05-06T15:39:00', 'YYYY-MM-DD"T"HH24:MI:SS');
A
--------------------------------------------------------------------------
10-APR-22 03.39.00.000000 PM
01-MAY-22 03.39.00.000000 PM

Answer 2

使用TIMESTAMP文字：

SELECT *
FROM   table_name
WHERE  column_name BETWEEN TIMESTAMP '2022-04-01 18:02:42'
AND     TIMESTAMP '2022-11-03 19:28:57';

或DATE和INTERVAL DAY TO SECOND文字：

SELECT *
FROM   table_name
WHERE  column_name BETWEEN DATE '2022-04-01' + INTERVAL '18:02:42' HOUR TO SECOND
AND     DATE '2022-11-03' + INTERVAL '19:28:57' HOUR TO SECOND;

或TO_TIMESTAMP:

SELECT *
FROM   table_name
WHERE  column_name BETWEEN TO_TIMESTAMP('2022-04-01 18:02:42', 'YYYY-MM-DD HH24:MI:SS')
AND     TO_TIMESTAMP('2022-11-03 19:28:57', 'YYYY-MM-DD HH24:MI:SS');

或TO_DATE:

SELECT *
FROM   table_name
WHERE  column_name BETWEEN TO_DATE('2022-04-01 18:02:42', 'YYYY-MM-DD HH24:MI:SS')
AND     TO_DATE('2022-11-03 19:28:57', 'YYYY-MM-DD HH24:MI:SS');

Answer 3

我也面临这个问题很长一段时间了，我的查询给了我结果，但也包括了之前和之后日期的额外记录，这是不对的。

OLD查询(所有注释的查询都运行，但给出错误的结果(：

select TO_0CHAR(time_stamp,'DD-MON-YYYY HH24:MI:SS') time_stamp, count(*) as count from TABLE_NAME where
--time_stamp between TO_TIMESTAMP('22-AUG-2023 00:00:00','DD-MON-YYYY HH24:MI:SS') and TO_TIMESTAMP('22-AUG-2023 23:59:59','DD-MON-YYYY HH24:MI:SS')
--time_stamp between TO_DATE('22-AUG-2023 00:00:00','DD-MON-YYYY HH24:MI:SS') and TO_DATE('22-AUG-2023 23:59:59','DD-MON-YYYY HH24:MI:SS')
--time_stamp >= TO_DATE('22-AUG-2023 00:00:00','DD-MON-YYYY HH24:MI:SS') and time_stamp <= TO_DATE('22-AUG-2023 23:59:59','DD-MON-YYYY HH24:MI:SS')    
group by time_stamp order by time_stamp asc;

它可能适用于不同的场景，如数字范围或仅日期等，但对于时间戳，它不能给出正确的结果。

新查询(正确结果！！(：

select TO_CHAR(time_stamp,'DD-MON-YYYY HH24:MI:SS') time_stamp, count(*) as count from TABLE_NAME where
TO_CHAR(time_stamp,'DD-MON-YYYY HH24:MI:SS') between '22-AUG-2023 00:00:00' and '22-AUG-2023 23:59:59'
group by time_stamp order by time_stamp asc;

在这个查询中，我们将日期转换为字符串，然后获取给定时间戳字符串之间的结果。

Answer 4

首先必须更改NLS_DATE_FORMAT，如下所示：ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';ALTER SESSION SET NLS_DATE_FORMAT = 'HH24:MI:SS';

然后使用TIMESTAMP文字运行查询，如下所示：

SELECT * FROM table_name WHERE column_name BETWEEN TIMESTAMP '2022-04-01 18:02:42' AND TIMESTAMP '2022-11-03 19:28:57';