Q
Problem solve Get help with specific problems with your technologies, process and projects.

Mapping time intervals with Oracle's date arithmetic

I'm having problems mapping time intervals in a time schedule. What I'm trying to do is, given a date interval...

(Ex: Start Time - 19-09-2002 05:30; End Time - 24-09-2002 15:00), I would like to know how many minutes were used in three intervals: Working Day from 08:00 to 22:00; Working Day from 22:00 to 08:00; Non Working Day. I would like to say that with that example, I had x minutes on the first interval, w minutes on the second interval and z minutes on the third interval. What's the best way to do this?

One of Oracle's most overlooked strengths is the ability to do date arithmetic without any conversions. For example, if you take two DATE datatypes and subtract them, Oracle will tell you the difference. Then, one can use formatting to display the results in any manner you wish. So let's see how we can use this to help solve your problem.

Let's look at how we determine how many minutes were used in the first interval. We have three scenarios, the time frame starts before the interval starts, the time frame starts after the interval is over, or the time frame starts in the middle of the interval. Assume time_start is a variable which holds the start time, time_end is a variable which holds the end time, inteval_start is the start of the interval, and interval_end is a variable which holds the end of the interval. The variable total_minutes will hold the total number of minutes spent in the interval.

```If time_start < interval_start then
-- time started before the interval begin
total_minutes = time_end - interval_start;
-- check for a negative value. if negative,
-- set to zero
if total_minutes < 0 then total_minutes=0;
else if time_start > interval_end then
-- time started after interval ended
-- so no time spent in this interval
total_minutes = 0;
else
-- time started in the middle of the interval
-- two choices now, time ends at the end of the
-- interval or time ends before the end of the
-- interval
if time_end < interval_end then
-- time spent total in this interval
total_minutes = time_end - time_start;
else
-- only take time from start to interval end
total_minutes = interval_end - time_start;
```

So we now need to convert this to SQL statements. Our IF-THEN choices will be replaced by DECODE statements. To test for less-than in our DECODE statement, we will use the SIGN function.

```SELECT
DECODE(SIGN(time_start-interval_start),-1,DECODE(SIGN(time_end-interval_start),-1,0,time_end-interval_start)),
DECODE(SIGN(interval_end-time_start),-1,0,
DECODE(SIGN(time_end-interval-end),-1,time_end-time_start,interval_end-time_start)))
FROM dual;
```

Hopefully, you can see how I was able to use DECODEs to make IF-THEN decisions and the SIGN function to test for inequalities. The number returned will be a floating point number. The integer portion is the number of days. The fractional portion is the fraction of a day. So to get this converted to minutes, just multiply this number by 1440 (24 hours * 60 minutes).

This can get quite messy, I agree. So you may wish to code this as a PL/SQL function and have the function return the number of minutes.

This was last published in September 2002

Have a question for an expert?

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

SearchDataManagement

• GDPR compliance requirements drive new winds of data privacy

Hello, GDPR. May 25 is the witching hour for enforcement of the EU's much-discussed GDPR compliance requirements -- and it's a ...

• Six data risk management steps for GDPR compliance

By layering data risk management processes into your data governance framework, you can ensure the personally identifiable ...

• AP uses data collaboration platform to spread data journalism

Data journalism reporters need tools that deliver quick context for stories on deadline. A data collaboration platform from ...

• Ten KPI templates for your dashboards

KPIs help companies gauge success, but how do you choose the right metrics to create useful reports? Here you'll find 10 KPI ...

• Data visualization process demands smart design, accurate data

Well-designed data visualizations can enable executives to make more-informed business decisions, increasing the potential ROI of...

• Seven good data visualization practices for visual integrity

Data visualizations need visual integrity to ensure that the data they present can be interpreted correctly. Follow these design ...

SearchSAP

• Inside SAP's digital transformation strategy

In this Q&A, Bertram Schulte, SAP's chief digital officer, discusses the how and why as the company goes through its own internal...

• SAP Cloud Platform pushes to break new ground, extend ERP

SAP wants its Cloud Platform to connect ERP systems with new technology like machine learning and IoT, resulting in new processes...

• SAP Cloud Platform architecture is glue for new technologies

SAP Cloud Platform has numerous integration tools for linking next-gen technologies like blockchain, machine learning and IoT to ...

SearchSQLServer

• Common Data Service for Analytics eases Power BI integration

Integrating data into Power BI for analysis can be a challenge, but Microsoft's Common Data Service for Analytics technology is ...

• Power BI Report Server adds new Power BI reporting options

Updates to Power BI Report Server expand the on-premises platform's reporting functionality and increase the number of data ...

• Azure Cosmos DB features, pricing morph with new provisioning model

The ability to provision Azure Cosmos DB throughput at the database level could come with pricing sticker shock for some users of...

TheServerSide.com

• How to embed Tomcat and Java web apps in an executable JAR

There's nothing magical about packaging Java web apps in an executable JAR file. This tutorials shows you how to embed Tomcat and...

• Why we still use Apache Ant tool in Java build and deploys

Plenty of companies still use the Apache Ant tool in Java development cycles and as part of their continuous deployments. Here's ...

• Packaging Java programs in the Docker and microservices age

The packaging of Java programs has taken an interesting turn, as the original JAR format becomes a favorite even for Java web ...

SearchDataCenter

• Get to know SIEM software basics and benefits

Security information and event management tools give organizations enhanced network management functionality, such as automatic ...

• Plexxi buy bolsters HPE composable infrastructure strategy

HPE strengthened its composable infrastructure strategy with the acquisition of Plexxi, a startup that adds a networking element ...

• Hadoop cluster configuration best practices streamline workflows

Organizations that deal with a variety of Hadoop configurations can streamline workflows through baseline configuration, tests ...

SearchContentManagement

• Five areas to focus on in a SharePoint and GDPR compliance strategy

SharePoint admins need to be ready for the General Data Protection Regulation, too. Expert Reda Chouffani suggests five things to...

• Workplace collaboration tool bolsters employee productivity

The benefits of workplace collaboration tools trickle down to employee relationships, company growth, community engagement and ...

• New age collaboration tools empower the future of ECM

Document management, file-sharing and ECM platforms might represent 'ancient' 1990s technology, but AI and collaboration tools ...

SearchHRSoftware

• Federal HR wants to modernize cybersecurity recruiting, pay

The U.S. Dept. of Homeland Security wants to modernize recruitment and management of its cybersecurity workforce. It is asking ...

• Huge potential of video interview software comes with risks

Tech-savvy millennials' advantage over older workers risks ageism, and AI needs work handling emotions and ethnicity. But few ...

• How to solve six of the biggest employee retention challenges

Recruiters are enticing workers with new opportunities on an increasingly frequent basis. Give your employees plenty of reasons ...

Close