Back to cookbooks list Articles Cookbook

How to Find the Difference Between Two Timestamps in Oracle

Database:

Operators:

  • -

Table of Contents

Problem

You have two columns of the type timestamp and you want to calculate the difference between them.

Example

In the travel table, there are three columns: id, departure, and arrival. You'd like to calculate the difference between the arrival and the departure.

The travel table looks like this:

iddeparturearrival
12018-03-25 12:00:002018-04-05 07:30:00
22019-09-12 15:50:002019-10-23 10:30:30
32018-07-14 16:15:002018-07-14 20:40:30
42018-01-05 08:35:002019-01-08 14:00:00

Solution

SELECT
  id,
  departure,
  arrival,
  arrival - departure AS difference
FROM travel;

The result is:

iddeparturearrivaldifference
12018-03-25 12:00:002018-04-05 07:30:0010 19:30:0.0
22019-09-12 15:50:002019-10-23 10:30:3040 18:40:30.0
32018-07-14 16:15:002018-07-14 20:40:300 4:25:30.0
42018-01-05 08:35:002019-01-08 14:00:00368 5:25:0.0

Discussion

To calculate the difference between the timestamps in Oracle, simply subtract the start timestamp from the end timestamp (here: arrival - departure). The resulting column will be in INTERVAL DAY TO SECOND. The first number you see is the number of whole days that passed from departure to arrival. Then you'll see the number of full hours, full minutes, and the number of seconds, with any partial second as the decimal place.

Recommended courses:

Recommended articles:

See also: