oracle oracle中如何选择两个时间戳之间的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26995944/
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
How to select records between two timestamps in oracle
提问by Luixv
I have a table where the column recordedis defined as a timestampas follows:
我有一个表，其中列recorded的定义timestamp如下：
TIMESTAMP(6) RECORDED
I want to select all the records which have been "recorded" between 2 timestamps . For that I wrote the following query:
我想选择在 2 个时间戳之间“记录”的所有记录。为此，我编写了以下查询：
SELECT * FROM myTable WHERE
recorded BETWEEN 1414794711050 AND 1415399511053;
When doing this I am getting an error
执行此操作时出现错误
ORA-00932: Inconsistent Data types: TIMESTAMP expected, NUMBER got 00932. 00000 - "inconsistent datatypes: expected %s got %s"
ORA-00932：不一致的数据类型：预期的 TIMESTAMP，NUMBER 得到 00932。00000 - “不一致的数据类型：预期 %s 得到 %s”
I also tried with:
我也试过：
SELECT * FROM myTable WHERE
recorded BETWEEN to_timestamp(1414794711) AND to_timestamp(1415399511);
How is the way get this query working?
这个查询是如何工作的？
回答by Multisync
SELECT * FROM myTable
WHERE recorded BETWEEN to_date('19700101', 'YYYYMMDD') + 1414794711050/24/60/60/1000
and to_date('19700101', 'YYYYMMDD') + 1415399511053/24/60/60/1000;
1414794711050/24/60/60/1000converts milliseconds to a number of days (24 hours in a day, 60 munites in a hour, 60 seconds in a minute)
1414794711050/24/60/60/1000将毫秒转换为天数（一天 24 小时，一小时 60 分钟，一分钟 60 秒）
to_date('19700101', 'YYYYMMDD')+ [N days] = new date (N days later)
to_date('19700101', 'YYYYMMDD')+ [N 天] = 新日期（N 天后）
