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';