oracle 在 SQL 中使用 Timestamp 拉错数据进行查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19321047/
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
Query in SQL using between Timestamp pulling wrong data
提问by Kairan
I am using this query:
我正在使用这个查询：
SELECT ts as "TimeStamp",
stat as "Status"
FROM myTable
WHERE stat = 'O'
AND source = 'Source1'
AND ts BETWEEN TO_TIMESTAMP('2013-10-05','yyyy-mm-dd') AND
TO_TIMESTAMP('2013-10-06','yyyy-mm-dd')
And also tried:
还尝试过：
SELECT ts as "TimeStamp",
stat as "Status"
FROM myTable
WHERE stat = 'O'
AND source = 'Source1'
AND ts >= TO_TIMESTAMP('2013-10-05','yyyy-mm-dd') AND ts <
TO_TIMESTAMP('2013-10-06','yyyy-mm-dd')
It returns 0 records, but if I do
它返回 0 条记录，但如果我这样做
SELECT ts as "TimeStamp",
stat as "Status"
FROM myTable
WHERE stat = 'O'
I can clearly identify 5 records. Apparently the TO_TIMESTAMPis not working properly I am hoping someone might be able to help identify the proper fix
我可以清楚地识别5条记录。显然TO_TIMESTAMP无法正常工作，我希望有人能够帮助确定正确的修复方法
Edit: To clarify, I only want the timeframe for 10/5 not including 10/6Sorry pasted wrong results Also the field is of type TIMESTAMP(6)
编辑：澄清一下，我只想要 10/5 的时间范围，不包括 10/6抱歉粘贴错误的结果而且该字段的类型为 TIMESTAMP(6)
回答by asantaballa
Because the TO time stamp has time even though you're not specifying. So it is selecting only up to the very beginning of the lat day. Either specify time 23:59:59 and subseconds as required, or do less than the next day.
因为即使您没有指定 TO 时间戳也有时间。所以它只选择到最后一天的开始。根据需要指定时间 23:59:59 和亚秒，或者小于第二天。
...
AND ts >= TO_TIMESTAMP('2013-10-05','yyyy-mm-dd')
AND ts < TO_TIMESTAMP('2013-10-07','yyyy-mm-dd')
回答by dElo
Try: AND ts BETWEEN TO_DATE('2013-10-05','YYYY-MM-DD') AND TO_DATE('2013-10-06','YYYY-MM-DD')
尝试： AND ts BETWEEN TO_DATE('2013-10-05','YYYY-MM-DD') AND TO_DATE('2013-10-06','YYYY-MM-DD')
And mind that it will get you results from the 10-05 at 00:00:00 to the 10-06 at 00:00:00 If you don't specify the HH MI SS mask.
请注意，如果您不指定 HH MI SS 掩码，它将从 00:00:00 的 10-05 到 00:00:00 的 10-06 获得结果。
Date masks are case sensitivve and TO_DATE will work better.
日期掩码区分大小写，TO_DATE 效果更好。
