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
This format is fixed. If you want to change it, get each unit by passing the interval to
So what if you’re looking for the difference between
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
timestamp in Oracle Database.
There are several differences between
timestamps can store more information:
timestampis the nanosecond; the granularity of dates is the second
timestampscan store time zone information;
As we saw above, the arithmetic for
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:
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,
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 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:
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
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:
For example, if you want the total milliseconds between
timestamps, get the difference and sum up the:
Or if you want to convert a
day to second interval to hours, add together the:
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:
day to second intervals, the format for this is fixed. To get the months or years separately, use
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
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.
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:
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:
When it comes to 31st Mar – 30th April the calculation could be either:
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 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:
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_betweengets the elapsed months from the first date to the second
last_dayreturns the final day in the input month. It preserves the input time
add_monthsincreases 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.
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:
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:
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!