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

Learn best practices according to DataOps. Download the free O'Reilly eBook on building a modern Big Data platform.

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.

Find the perfect platform for a scalable self-service model to manage Big Data workloads in the Cloud. Download the free O'Reilly eBook to learn more.

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

Published at DZone with permission of Shay Shmeltzer, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}