In this article we are going to discuss how an user can store a date using ETL and Groovy. In order to test the code you need to be logged in via the webinterface and make use of the ETL manager.

In case of storing a date in Jedox the user has two options. Either the user stores the date in an element/attribute of type string or of type numeric. Both cases are acceptable, however storing the date in numerical value will provide the user with more flexibility in terms of manipulation. Granted that storing the data as a numerical value will take more effort, this topic is devoted to reduce that effort and discuss the possibilities.

Let’s suppose a loan has to be repaid within 60 days. The user would have to store the current day and calculate the deadline of the loan. In case the date has been transformed to a number, the user can easily add 60 to the current value without having taking into account the length of a month.

The code to accomplish this:

When using this example in a function of type Groovy in a FieldTransform, only the first 3 lines of code are required (minus the // which results in the skipping of that line of code). The first line defines a variable which can access the functionalities provided by Date class. In the next line of code the currentTime will be retrieved. The getTime() method returns how many milliseconds have passed since January 1, 1970, 00:00:00 GMT and is the reason why it will be stored in a variable of type long.

As currentTime is in milliseconds we divide the value by a 1000 to return the amount of seconds. Then we divide the number by 3600 to return the amount of hours. As currentTime does not take into consideration the different timezones, the TimezoneOffset will need to be deducted. The getTimezoneOffset() method returns the time difference between UTC time and local time in minutes (UTC time is the same as GMT time). For example, if your time zone is GMT+1, -60 will be returned. It is important to note that the returned value is not a constant, because of the practice of using Daylight Saving Time. To transfer the value to days is done by dividing it by 24.

Lastly the user has to add 25569 to compensate for the different starting points in time between Microsoft Excel and Groovy. Microsoft Excel uses as a starting point January 1, 1900. In Groovy the starting point is January 1, 1970. When entering the value 25569 in Excel and changing the cell properties to date, it will show January 1, 1970.

Below is a more sophisticated example and can be used in the Function Editor of the FieldTransform and in a Groovy Job by pasting the exact code below. In case of using it in the Function Editor the first line can be removed and replaced by an input (see picture) and thus manipulating the date based on dynamic values. Also remove the // before return and remove the line of code starting with

Jedox ETL – Date Time