Chris Saxon

Developer Advocate

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.

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!

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.

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?

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

:

As with `day to second interval`

s, the format for this is fixed. To get the months or years separately, use `extract`

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.

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.

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.

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

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

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