How to get the difference between datetimes in days, hours, minutes, and/or seconds in Oracle Database

When you subtract one date from another in Oracle Database the result is the number of days between them. To see this as the number of days, hours, minutes, and seconds pass this to numtodstinterval with the units day. This returns a day to second interval, which is in the format DD HH24:MI:SS:

This format is fixed. If you want to change it, get each unit by passing the interval to extract:

So what if you’re looking for the difference between timestamp values?

The result of this is already a day to second interval! So just run the calculation and format as needed:

So far so easy. But what if you need the result in a single unit, e.g. the total number of hours or seconds? Or want to get the duration in years and months?

Read on to see how to:

• Calculate the hours between two dates
• Find the milliseconds from one timestamp to another
• Get the years and months between timestamps
• Return the months between dates
• Find the difference between datetimes in years down to seconds
• Define default formatting for datetime data types
• Cheat sheet

Before we dive into the details a few notes on the differences between date and timestamp in Oracle Database.

Date and timestamp basics in Oracle Database

There are several differences between dates and timestamps. First, timestamps can store more information:

• The maximum precision for timestamp is the nanosecond; the granularity of dates is the second
• timestamps can store time zone information; dates cannot

As we saw above, the arithmetic for date and timestamp values work differently too. The units for dates are the number of days, timestamps use intervals. When you mix and match these data types the outcomes are:

• Date +/- interval => timestamp
• Timestamp +/- number => timestamp
• Date – timestamp => number (of days)
• Timestamp – date => number (of days)

Notice that adding durations works differently to subtracting datetime values. Further complicating this, many datetime functions always return a date, even if the input is a timestamp. For example, trunc, add_months, and last_day.

So it’s easy to change the data type in complex datetime calculations. This can lead to losing information or other unwanted results. Take care and test datetime functions thoroughly!

Get the number of hours between two dates

Instead of formatted days, hours, minutes, and seconds, you may want the total hours between two dates. For example, from midnight on 17^th April to noon on 19^th April in the same year is 60 hours.

As the difference between dates is the number of days, all you need to do is multiply the result by how many of the unit you want in there are in a day:

• Days to hours => 24
• Days to minutes => 24 hours * 60 minutes => 1,440
• Days to seconds => 1,440 minutes * 60 seconds => 86,400

Then round the output as needed. For example, this gets the total number of hours, minutes, and seconds between dates:

Getting the duration between timestamps in one unit is trickier.

Calculate the number of (milli) seconds between two timestamps

There are no implicit conversions between intervals and numbers. So if you want to normalize the difference between two timestamps to one unit, you need to extract the fields out. After doing this add together:

• The larger units multiplied by how many of the unit you want there are in each
• The unit you want
• The next smallest unit divided by how many there are in the unit of interest

For example, if you want the total milliseconds between timestamps, get the difference and sum up the:

• days * 86,400,000 (milliseconds in a day)
• hours * 3,600,000 (milliseconds in an hour)
• minutes * 60,000 (milliseconds in a minute)
• seconds * 1,000 (milliseconds in a second)

Or if you want to convert a day to second interval to hours, add together the:

• days * 24 (hours in a day)
• hours
• minutes / 60 (minutes in an hour)

Then round the result as needed, which looks like:

So far we’ve used units smaller than the day. What if you want to go the other way and get the difference between datetimes in months or years?

Get the years and months between timestamps

To get the years between timestamps, cast the result to a year to month interval:

As with day to second intervals, the format for this is fixed. To get the months or years separately, use extract

Get the number of months between two dates

The months_between function returns the elapsed months going from the second date to the first. It returns a whole month (no fraction) if both dates have the same day-of-month or both are the last day of the month. The nls_calendar parameter defines the last day settings. To convert this to years and months, pass this to numtoyminterval:

Months_between uses a fixed 31-day month to calculate fractions. This can give surprising results for shorter months. For example, 30^th is the last day in March, so 30^th March to 1^st April is one day.

But months_between calculates the difference between these dates as ~0.0645. Multiplied up by 31 gives two days!

This is a general problem when converting days to months. The number of days in a month varies between 28 and 31, making it tricky to calculate fractional months. An alternative is show number of complete months, followed by the days.

Get the difference between datetimes in years, months, and days down to seconds

The basic approach to show all duration units for a period is:

• Find the complete months to get the years and months.
• Then find the days to seconds by subtracting the start date plus these complete months from the end date.

The key is you need to define what a "complete month" is. This a surprisingly hard problem; mindlessly copying code to do it is likely to give you unwanted results.

Consider a start date of 31^st March. This is the last day of the month. How many months and days are there between this and 28^th Feb (in non-leap years) or 30^th April?

Going from 28^th Feb to 31^st March you could say:

• 28^th Feb – 28^th Mar is one complete month, so 28^th Feb – 31^st Mar is one month and three days
• Both 28^th Feb and 31^st Mar are the last day in the month, so the difference between them is one month and zero days.

When it comes to 31^st Mar – 30^th April the calculation could be either:

• 30^th is before 31^st, so there's no months and 30 days between them
• Both dates are the last in the month, so the gap between them is one month and zero days

As my Twitter polls shows, opinions are split on which of these methods are correct:

There are other ways you could solve this problem. For example, by declaring 28^th to be the last day in every month or assuming there’s 30 days in every month. There are pros and cons to every method. Ensure you speak with the data consumers to agree how this should work!

For the examples in this post I’ll assume that a month is complete when either:

• The start and end dates are both the last day of the month
• The start and end dates have the same day of month and neither are the last day in the month (e.g. 16^th).

If only one input is the last day in the month, the month is incomplete. Instead show the number of days from the day-of-month for the start date in the month before the end and the end day-of-month.

Here's a function to get the full breakdown of years down to seconds between dates using the logic above, extended to account for the time of day too:

So what's going on here?!

The algorithm is based on these functions:

• months_between gets the elapsed months from the first date to the second
• last_day returns the final day in the input month. It preserves the input time
• add_months increases the input date by the specified number of months

The function rounds down the output of months_between to find the complete months between the dates. Months_between assumes a month is complete if both dates have the same day-of-month or they're both the last day of their month.

This means months_between calculates 30^th April to 30^th May as one month. This conflicts with our definition above which says this should be one month and 30 days (as 31^st is the final day in May). To overcome this, subtract one from the complete months if:

• Either date is the last day in the months AND
• The day of month for the other date is same or later.

Do NOT do this if both dates are the last date of the month and the end time of day is later than the start.

With the months calculated, to get the number of days:

• Add the number of complete months to the start date using add_months
• Subtract this date from the end_date

This gives you the years to months and days to seconds separately. To make the final output, extract the fields from these values and format as desired.

Here it is in action:

Phew! It works!

With so many different ways to show durations, you may want to standardize this for database columns. For example, to show the current age for birth dates in months and years. You can now do this as part of the data type definition in 23c using SQL usage domains.

Create reusable types for formatting datetime differences

Starting in Oracle Database 23c you can define usage domains. You can use these to extend basic data types with defaults, constraints, display, and sorting rules. When you apply a domain to columns, the columns get these properties from the domain.

Oracle Database 23c Free – Developer Release is the first release of the next-generation Oracle Database, allowing developers a head-start on building applications with innovative 23c features that simplify development of modern data-driven apps. The entire feature set of Oracle Database 23c is planned to be generally available within the next 12 months.

For example, this creates the domain birth_date. It has a check constraint to ensure that the date is midnight, effectively making it a date with no time. The display expression finds the months_between the current date and the domain value. It then converts this into a year to month interval to return the age:

Use this domain as the data type when creating tables and you can only insert dates with no time (or midnight) into the associated columns. To activate the display expression, pass the domain column to domain_display to see how old someone is:

It's likely you already have tables date of birth columns. Domains extend existing data types. So when you upgrade to 23c you can apply the domain to these with alter table. Use these commands to remove then reassociate the domain with the column:

If you have any questions or comments about SQL domains or other 23c functionality we'd love to hear them. Please submit them on the 23c Free forum.

Cheat sheet

Here's a cheat sheet for converting datetime differences to specific units

Want to learn more about datetime values in Oracle Database?

Watch the recording of my Ask TOM Office Hours video on how to use dates, timestamps, and intervals in Oracle Database: