Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle query for time difference in seconds

Tags:

sql

oracle

I am looking for a query to find the gap between two time fields.

Table A and table B in both COL3 contain time value. 8:13:54. COL3 Is a Char field.

Table B also having same fields. I Want to query on the following conditions.

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?

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))) )

like image 498
user3080572 Avatar asked Dec 10 '13 19:12

user3080572


1 Answers

Something like this:

select (laterDate - earlierDate) * 24 * 60 * 60 seconds
from etc

In oracle, dates are numbers. Each integer increment represents one calendar day.

like image 132
Dan Bracuk Avatar answered Nov 06 '22 08:11

Dan Bracuk