Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Date Calculations and Queries With Oracle Visual Builder Cloud Service

DZone's Guide to

Date Calculations and Queries With Oracle Visual Builder Cloud Service

When it comes to calculations and queries based on a date in Oracle Visual Builder Cloud Service, you'll need some JavaScript calculations. Here are some useful things to know if you're trying to do that.

· Big Data Zone ·
Free Resource

Hortonworks Sandbox for HDP and HDF is your chance to get started on learning, developing, testing and trying out new features. Each download comes preconfigured with interactive tutorials, sample data and developments from the Apache community.

It's very easy to define a field in a custom object in Oracle Visual Builder Cloud Service to store a date, but when it comes to doing calculations and queries based on date, you'll find that you need to resort to a little bit of JavaScript calculations.

Here are a couple of useful things to know if you are trying to do that.

Calculating Age, or Time Passed From a Date in Years

Let's assume you are storing information about employees and one of the pieces of information you have is their date of birth — the Birthday field in the image below.

How do you show their actual age in years on a page?

You can define a calculated field in your business object — and have VBCS use the Calculate value with formula as the source for this field.

Your formula would be something like:

(new Date() -new Date($birthdate) )/ (60*60*24*1000*365)

You are calculating the difference between today's date and the birthday field, and since the answer is in milliseconds ,you convert it to years by dividing by the number of milliseconds in a year.

Note that as you type in your formula, the dialog shows you the results of the formula below the formula field — quite useful to verify that you are doing it right.

Now, your page can show the age of your employees:

Filtering Based on Date

What if you wanted to limit the records shown in the table above to only show employees of a specific age?

The tricky part is that you'll need to do the calculation against the birthday field and not against the age field. The age field is not actually stored anywhere — rather, it is calculated on the fly.

Let's take the table shown above and assume we want to limit it to show employees who are younger than nine years. To do that, we'll add a query condition to our table to check that the birthday is larger than the date of (today minus 9 years).

The calculation of the date nine years ago will be done with a formula like this:

new Date($current_date-9*365*24*60*60*1000)

Now your table only shows older employees.

Want to have a more dynamic way to define the query criteria? You can adopt the approach I showed in this article, along with the techniques shown here.

One last note: Since not every year has 365 days, the calculation for milliseconds conversion is not completely accurate, but it is quite close.

Hortonworks Community Connection (HCC) is an online collaboration destination for developers, DevOps, customers and partners to get answers to questions, collaborate on technical articles and share code examples from GitHub.  Join the discussion.

Topics:
big data ,tutorial ,queries ,oracle visual builder cloud service ,date calculations ,data analytics ,javascript

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}