USER MANUALS

Dealing with Datetime and Interval Types

This section explains how to develop custom functions with input and/or output parameters of one of the datetime types.

The table below is the mapping between data types of Denodo and Java classes.

Type in Denodo

Java Class of the Package java.time

localdate

java.time.LocalDate

time

java.time.LocalTime

timestamp

java.time.LocalDateTime

timestamptz

java.time.ZonedDateTime and java.util.Calendar

intervalyearmonth

java.time.Period

intervaldaysecond

java.time.Duration

For example, to build a custom function with an input parameter of type intervalyearmonth, the class of the input parameter has to be a java.time.Period.

To return a timestamptz, the function has to return a ZonedDateTime or a Calendar.

Below you can find the implementation of a function with two signatures:

@CustomElement(type = CustomElementType.VDPFUNCTION, name = "MY_CUSTOM_FUNCTION")
public class MyCustomFunction {

    @CustomExecutor
    /*
    * Signature #1 has two input parameters: a ZonedDateTime and an Integer.
    * The execution engine considers that the types of the input parameters are
    * timestamptz and int respectively
    *
    * It returns a ZonedDateTime. The execution engines converts this into a timestamptz.
    */
    public ZonedDateTime execute(@CustomParam(name = "input") ZonedDateTime input,
            @CustomParam(name = "increment") Integer increment) {

        if (input == null || increment == null) {

            return null;

        } else {

            return input.plusHours(increment);
        }
    }

     @CustomExecutor
     /*
     * Signature #2 has two input parameters: a Duration and a Long.
     * The execution engine considers that the types of the input parameters are
     * intervaldaysecond and long respectively
     *
     * It returns a Duration object. The execution engines converts this into a intervaldaysecond.
     */
    public Duration execute(@CustomParam(name = "input") Duration input,
             @CustomParam(name = "increment") Long increment) {

         if (input == null || increment == null) {
             return null;

         } else {

             return input.plusHours(increment);
         }
    }
}

To receive the locale of the query, add a parameter of the type java.util.Locale and annotate it with @CustomLocale. The execution will inject the Local receive the locale of the query.

For example,

@CustomExecutor
public ZonedDateTime execute(@CustomParam(name = "input") ZonedDateTime input,
     @CustomParam(name = "increment") Integer increment, @CustomLocale Locale locale) {...

In previous versions, the class Calendar is mapped to the type date (deprecated). Starting with Denodo 7.0, the class Calendar is mapped to timestamptz. If you still want to map Calendar to date, execute the following command from the VQL Shell and restart the server.

SET 'com.denodo.vdb.compatibility.datetime.custom.mapCalendarToDateType' = 'true';
Add feedback