1

How can I subtract one timestamp from another in Oracle?

for example I have:

timestamp_1 = 2021-06-25 08:18:17,207141  
timestamp_2 = 2021-06-24 17:41:06,787111

how I can take a different between?

I need a diff in seconds

Ergest Basha
  • 7,870
  • 4
  • 8
  • 28
SQL_Python_DataScience
  • 69
  • 6
  • 1
    Does this answer your question? [Calculate difference between 2 date / times in Oracle SQL](https://stackoverflow.com/questions/1096853/calculate-difference-between-2-date-times-in-oracle-sql) – PM 77-1 Mar 31 '22 at 13:45
  • The term to search for in the future when Googling about things like this is "date arithmetic". – Andy Lester Mar 31 '22 at 13:55

1 Answers1

2

You can simply subtract the two values from each other

SELECT   TO_TIMESTAMP ('2021-06-25 08:18:17,207141', 'YYYY-MM-DD HH24:MI:SS,FF')
       - TO_TIMESTAMP ('2021-06-24 17:41:06,787111', 'YYYY-MM-DD HH24:MI:SS,FF')
  FROM DUAL;


+00 14:37:10.420030
EJ Egyed
  • 5,791
  • 1
  • 8
  • 23
  • 2
    @SQL_Python_DataScience - just keep clear in your mind that when you subtract dates/times from each other, what you get is not a date or a time of day, but an _elapsed time_. So the '14:37:01.420030' result in Ej Egyed's answer is not 2:37:01 PM, but 14 hours, 37 minutes, 1 second and 420030 nanoseconds. You may say 'well, that's obvious', but you'd be surprised at how many people somehow miss that point. – EdStevens Mar 31 '22 at 14:40