SQL Oracle查询以秒为单位的时差
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20503376/
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 query for time difference in seconds
提问by user3080572
I am looking for a query to find the gap between two time fields.
我正在寻找一个查询来查找两个时间字段之间的差距。
Table Aand table Bin both COL3contain time value. 8:13:54. COL3Is a Char field.
表A和表B中都COL3包含时间值。8:13:54. COL3是一个字符字段。
Table Balso having same fields. I Want to query on the following conditions.
表B也具有相同的字段。我想查询以下条件。
A.COL1 = B.COL1 and
A.COL2 = B.COL2 and
(COL3 in a should equal to + / - 120 secs of COL B).
I tried to use TO_DATE, but its not working out. How can i achieve this?
我尝试使用TO_DATE，但它不起作用。我怎样才能做到这一点？
This works for me. Thanks.
这对我有用。谢谢。
SELECT A., B.FROM A, B WHERE A.COL1 = B.COL1 AND A.COL2 = B.COL2 AND ( TO_DATE(B.COL3,'hh:mi:ss') = (TO_DATE(B.COL3,'hh:mi:ss') + (120/(24*60*60))) OR TO_DATE(B.COL3,'hh:mi:ss') = (TO_DATE(B.COL3,'hh:mi:ss') - (120/(24*60*60))) )
SELECT A. , B.FROM A, B WHERE A.COL1 = B.COL1 AND A.COL2 = B.COL2 AND ( TO_DATE(B.COL3,'hh:mi:ss') = (TO_DATE(B.COL3, 'hh:mi:ss') + (120/(24*60*60))) 或 TO_DATE(B.COL3,'hh:mi:ss') = (TO_DATE(B.COL3,'hh:mi:ss) ') - (120/(24*60*60))))
回答by Dan Bracuk
Something like this:
像这样的东西：
select (laterDate - earlierDate) * 24 * 60 * 60 seconds
from etc
In oracle, dates are numbers. Each integer increment represents one calendar day.
在 oracle 中，日期是数字。每个整数增量代表一个日历日。
