Calculating difference between two timestamps in Oracle in milliseconds

Oracle timestamp arithmetic decoded

When you subtract timestamps in Oracle, you receive an interval between them. This interval comprises days, hours, minutes, and seconds, retrievable with the handy EXTRACT function. To convert these components into a millisecond format, multiply them by respective millisecond values.

-- Who knew time could be bought and sold by the milliseconds? Not Martin McFly!


On different platforms, Oracle might store interval values as milliseconds or microseconds, so ensure you factor in your platform's precision when doing your calculations.

Tackling time zones & daylight savings

The complexities of time zones and daylight saving time can influence the accuracy of timestamp differences. Ensure both timestamps are in the same time zone for precise calculations using the AT TIME ZONE clause:

SELECT
    (EXTRACT(DAY FROM diff) * 86400000) +
    (EXTRACT(HOUR FROM diff) * 3600000) +
    (EXTRACT(MINUTE FROM diff) * 60000) +
    (EXTRACT(SECOND FROM diff) * 1000) AS milliseconds
FROM (
    SELECT TO_DSINTERVAL(
        TIMESTAMP WITH TIME ZONE 'END TIMESTAMP'
        AT TIME ZONE 'UTC' - 
        TIMESTAMP WITH TIME ZONE 'START TIMESTAMP'
        AT TIME ZONE 'UTC'
    ) diff
    FROM your_table
)

-- Remember, time is relative. So is your aunt Sue, but that's a different issue.


Swap 'END TIMESTAMP' and 'START TIMESTAMP' with your timestamps.

Creating a neat custom function

To bulldoze the redundancy of going through the calculation every time, create a custom function encapsulating the logic. This is your magic wand for efficient calculations.

CREATE OR REPLACE FUNCTION calculate_milliseconds_difference(start TIMESTAMP, end TIMESTAMP) RETURN NUMBER IS
    total_milliseconds NUMBER;
BEGIN
    SELECT
        (EXTRACT(DAY FROM diff) * 86400000) +
        (EXTRACT(HOUR FROM diff) * 3600000) +
        (EXTRACT(MINUTE FROM diff) * 60000) +
        (EXTRACT(SECOND FROM diff) * 1000)
    INTO total_milliseconds
    FROM (
        SELECT TO_DSINTERVAL(end - start) diff FROM DUAL
    );
    RETURN total_milliseconds;
END;

-- Because we'd like to outsource our math when we can.


Invoke this function with your timestamps as shown:

SELECT calculate_milliseconds_difference(start, end) FROM your_table;

-- Just call upon our function genie. Voila!


Timestamp arithmetic: Best practices

1. Match your formulas with platform precision.
2. Use NUMTODSINTERVAL for quick conversion of intervals to seconds.
3. Document your code for easier maintenance.
4. Leverage SELECT statements to break down interval components methodically.
5. Ensure compatibility of your methods with a broad range of timestamp values.
6. Create customizable functions for project-specific needs.
7. Understand Oracle's date and time formats to ensure accurate conversions.

-- "Best practices"... because we like to dot our i's, cross our t's and optimize our SQL queries.
