Subtracting two timestamps in Oracle returns an INTERVAL DAY TO SECOND, which is exact but awkward when all you want is a number of minutes or seconds. TIMESTAMPDIFF, new in Oracle AI Database 26ai, returns the difference between two timestamps as a number in the unit you name. It is another name for DATEDIFF, and the name most developers coming from other databases look for when working with times.
Code for This Guide
The main example is in the examples/date-functions folder of the Oracle Database 26ai code repository on GitHub, with its output. The repository also has the NIMBUS sample schema in the setup/nimbus folder.
It comes from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
timestampdiff(time_unit, start_datetime, end_datetime [, start_of_period])
time_unit is HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND, or NANOSECOND for times, or YEAR, QUARTER, MONTH, WEEK, or DAY. Like DATEDIFF, it counts the boundaries of the unit crossed between the two values.
Minutes Between Two Times
The last column of this query uses TIMESTAMPDIFF to count the minutes between 08:00 and 10:45.
Example:
select datediff(day, date '2026-01-01', date '2026-03-15') as days,
datediff(week, date '2026-01-01', date '2026-03-15') as weeks,
datediff(month, date '2026-01-31', date '2026-02-01') as month_boundaries,
datediff(year, date '2025-12-31', date '2026-01-01') as year_boundaries,
timestampdiff(minute, timestamp '2026-03-15 08:00:00',
timestamp '2026-03-15 10:45:00') as minutes
from dual;
select flight_no, datediff(minute, scheduled_departure, actual_departure) as minutes_late
from flights
where flight_id between 20 and 24;Output:
DAYS WEEKS MONTH_BOUNDARIES YEAR_BOUNDARIES MINUTES
_______ ________ ___________________ __________________ __________
73 11 1 1 165
FLIGHT_NO MINUTES_LATE
____________ _______________
NM149 2
NM133 0
NM121 0
NM142 69
NM118 17The second query uses the DATEDIFF name for the same calculation on real flights, the minutes between scheduled and actual departure.
Time Zones, Daylight Saving Time, and Fractions
With TIMESTAMP WITH TIME ZONE values, TIMESTAMPDIFF measures real elapsed time, so it handles different time zones and clock changes correctly.
Example:
-- the night clocks moved forward in New York: three wall-clock hours, two real hours
select timestampdiff(hour, timestamp '2026-03-08 00:30:00 America/New_York',
timestamp '2026-03-08 03:30:00 America/New_York') as hours,
timestamp '2026-03-08 03:30:00 America/New_York'
- timestamp '2026-03-08 00:30:00 America/New_York' as interval_diff
from dual;
-- across time zones: London to Dubai
select timestampdiff(minute, timestamp '2026-03-15 08:00:00 Europe/London',
timestamp '2026-03-15 18:55:00 Asia/Dubai') as minutes,
timestampdiff(second, timestamp '2026-03-15 08:00:00.250 UTC',
timestamp '2026-03-15 08:00:01.100 UTC') as seconds,
timestampdiff(millisecond, timestamp '2026-03-15 08:00:00.250 UTC',
timestamp '2026-03-15 08:00:01.100 UTC') as ms
from dual;Output:
HOURS INTERVAL_DIFF
________ ______________________
2 +00 02:00:00.000000
MINUTES SECONDS MS
__________ __________ ______
415 1 850On the night New York moved its clocks forward, 00:30 to 03:30 on the wall is only two hours, matching the interval that subtraction returns. From 08:00 in London to 18:55 in Dubai is 415 minutes, because Dubai is four hours ahead. Between 08:00:00.250 and 08:00:01.100, one second boundary is crossed, but 850 milliseconds elapse: choose the unit that matches the precision you need.
TIMESTAMPDIFF Compared with Subtraction
| Method | Returns |
|---|---|
| end - start | An INTERVAL DAY TO SECOND, exact to the fraction |
| extract(...) from (end - start) | One field of the interval, such as the minutes part only |
| timestampdiff(minute, start, end) | A NUMBER: minute boundaries crossed |
Related Guides
Conclusion
TIMESTAMPDIFF returns the difference between two timestamps as a number of hours, minutes, seconds, or finer units, counting unit boundaries like DATEDIFF, of which it is another name. With time zone values it measures real elapsed time across zones and daylight saving changes.
