SQL oracle sql查询时间戳落在两个时间戳之间的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30070691/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
oracle sql query for records with timestamp that falls between two timestamps
提问by user1746050
I have two timestamps in String format 2015-05-06T15:39:00and 2015-04-06T15:39:00.
我有两个字符串格式的时间戳2015-05-06T15:39:00和2015-04-06T15:39:00.
What is the sql query for Oracle that I can query all the records in the table that has timestamp that falls within this range.
什么是 Oracle 的 sql 查询，我可以查询表中时间戳落在此范围内的所有记录。
回答by Moudiz
And with alternative way you can use between
并通过替代方式，您可以在两者之间使用
SELECT *
FROM tab1
WHERE timestamps BETWEEN TO_DATE ('2015-05-06T15:39:00', 'YYYY-MM-DD"T"HH24:MI:SS') AND TO_DATE('2015-04-06T15:39:00', 'YYYY-MM-DD"T"HH24:MI:SS');
回答by sqluser
SELECT *
FROM yourTable
WHERE timestamps >= TO_DATE('2015-05-06T15:39:00', 'YYYY-MM-DD"T"HH24:MI:SS')
AND timestamps <= TO_DATE('2015-04-06T15:39:00', 'YYYY-MM-DD"T"HH24:MI:SS')
回答by tk_
None of the above answers worked for me.
以上答案都不适合我。
however, First simply replace 'T' you have with a ' '(space) and use below query which worked for me
但是，首先简单地用''（空格）替换'T'，然后使用以下对我有用的查询
select * from tb1
where timestamps BETWEEN TO_DATE ('2015-05-06 15:39:00', 'YYYY-mm-dd HH24:MI:SS')
AND TO_DATE('2015-04-06 15:39:00', 'YYYY-mm-dd HH24:MI:SS');
回答by user9148631
The below one is for timestamp and you can change for your required time
以下是时间戳，您可以根据需要更改
SELECT *
FROM tbl1
WHERE timestamp BETWEEN to_date('21/11/2017 23:59:59','dd/MM/rrrr hh24:mi:ss')
AND to_date('21/12/2017 15:59:59','dd/MM/rrrr hh24:mi:ss');`
回答by Lalit Kumar B
You need to convert the literal into DATE using TO_DATEand required format maskto compare the timestamp column with the input timestampvalues.
您需要使用TO_DATE和所需的格式掩码将文字转换为 DATE ，以将时间戳列与输入时间戳值进行比较。
Setup
设置
SQL> CREATE TABLE t(A TIMESTAMP);
Table created.
SQL>
SQL> INSERT INTO t(A) VALUES(to_date('2015-04-10T15:39:00', 'YYYY-MM-DD"T"HH24:MI:SS'));
1 row created.
SQL> INSERT INTO t(A) VALUES(to_date('2015-05-01T15:39:00', 'YYYY-MM-DD"T"HH24:MI:SS'));
1 row created.
SQL> INSERT INTO t(A) VALUES(to_date('2015-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-15 03.39.00.000000 PM
01-MAY-15 03.39.00.000000 PM
01-MAR-15 03.39.00.000000 PM
Query
询问
SQL> SELECT *
2 FROM t
3 WHERE A BETWEEN
4 to_date('2015-04-06T15:39:00', 'YYYY-MM-DD"T"HH24:MI:SS')
5 AND
6 to_date('2015-05-06T15:39:00', 'YYYY-MM-DD"T"HH24:MI:SS');
A
--------------------------------------------------------------------------
10-APR-15 03.39.00.000000 PM
01-MAY-15 03.39.00.000000 PM
So, I got the required rows as my desired output.
所以，我得到了所需的行作为我想要的输出。
