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

April 27, 2023 | 9 minute read
Chris Saxon
Developer Advocate
Text Size 100%:

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:

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!

Vintage alarm clock
Photo courtesy of Gratisography

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 17th April to noon on 19th 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?

Calendars

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, 30th is the last day in March, so 30th March to 1st 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 31st March. This is the last day of the month. How many months and days are there between this and 28th Feb (in non-leap years) or 30th April?

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

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

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

  • 30th is before 31st, 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:

Twitter poll results

There are other ways you could solve this problem. For example, by declaring 28th 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. 16th).

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 30th April to 30th May as one month. This conflicts with our definition above which says this should be one month and 30 days (as 31st 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 Oracle Database 23ai using data use case domains.

Create reusable types for formatting datetime differences

Starting in Oracle Database 23ai you can define use case 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 23ai is now generally available. Learn more about the SQL improvements in Oracle Database 23ai.

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 Oracle Database 23ai 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 use case domains or other 23ai functionality we'd love to hear them. Please submit them on the 23ai Free forum.

Cheat sheet

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

Cheat sheet for calculating datetime differences in various 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:

Chris Saxon

Developer Advocate

Chris Saxon is an Oracle Developer Advocate for SQL. His job is to help you get the best out of the Oracle Database and have fun with SQL!

To help you with this he blogs at All Things SQL. He also creates videos combining SQL and magic on YouTube at the The Magic of SQL.

If you have questions about working with Oracle Database technology, please reach out to him. You can do this via Twitter or on Ask Tom.


Previous Post

How to write an anonymous PL/SQL function and use it in your SQL statement

Gerald Venzl | 1 min read

Next Post


Announcing the General Availability of the SQL:2023 Standard

Gerald Venzl | 2 min read