how to find difference between two dates in oracle

Question

Accepted Answer

In Oracle, subtracting one `DATE` value from another gives you the difference directly in **days** (as a number), because Oracle stores dates internally as numbers. To get differences in other units, or when working with `TIMESTAMP` values, you need slightly different approaches.

**1. Difference in days (DATE columns)**
```sql
SELECT date1 - date2 AS days_diff
FROM your_table;
```
This returns a numeric value representing the number of days (can include a fractional part if time components differ).

**2. Difference in hours, minutes, or seconds**
Since subtracting two dates gives days, multiply to convert:
```sql
SELECT (date1 - date2) * 24        AS hours_diff,
       (date1 - date2) * 24 * 60   AS minutes_diff,
       (date1 - date2) * 24*60*60  AS seconds_diff
FROM your_table;
```

**3. Difference in months or years**
Use the built-in `MONTHS_BETWEEN` function:
```sql
SELECT MONTHS_BETWEEN(date1, date2) AS months_diff
FROM your_table;
```
Divide by 12 for an approximate year difference, or use `EXTRACT` combined with subtraction logic for exact calendar-based year differences.

**4. Difference between TIMESTAMP values**
Subtracting two `TIMESTAMP` values returns an `INTERVAL DAY TO SECOND` literal rather than a plain number:
```sql
SELECT timestamp1 - timestamp2 AS interval_diff
FROM your_table;
```
You can then use `EXTRACT` to pull out specific components:
```sql
SELECT EXTRACT(DAY FROM (timestamp1 - timestamp2))    AS days,
       EXTRACT(HOUR FROM (timestamp1 - timest