Difference between 2 timestamps in Oracle SQL

Question

Please help in finding the difference between 2 timestamps in SQL

I tried using

Select (cast(sysdate as timestamp) - cast(sysdate-2 as timestamp)) diff from dual;

I got 'ORA-00911:invalid character' error

Answer 1

If you want something that looks a bit simpler, try this for finding events in a table which occurred in the past 1 minute:

With this entry you can fiddle with the decimal values till you get the minute value that you want. The value .0007 happens to be 1 minute as far as the sysdate significant digits are concerned. You can use multiples of that to get any other value that you want:

select (sysdate - (sysdate - .0007)) * 1440 from dual;

Result is 1 (minute)

Then it is a simple matter to check for

select * from my_table where (sysdate - transdate) < .00071;

Answer 2

You are not finding the difference between two timestamps; you are finding the difference between two dates and casting them to timestamps in the process. This is unnecessary and you can just leave them as date values and explicitly cast the difference to an INTERVAL DAY TO SECOND data type (which would be the output if you used timestamps):

SELECT (SYSDATE - (SYSDATE - 2)) DAY TO SECOND AS diff
FROM   DUAL;

Which outputs the interval:

DIFF
+02 00:00:00.000000

---

However, your code works db<>fiddle.

You will probably find that the error:

ORA-00911:invalid character

Is nothing to do with your query and is related to the ; statement terminator at the end. If you are running your query through a 3rd-party application (i.e. C#, Java, Python, etc.) then they will pass single statements to the database to execute and, in this case, having the statement terminator is invalid syntax (as there will only be one SQL statement) and you just need to remove it.