How to use MySQL DATE_ADD function when creating Calculated Fields in Adhoc View

Problem :  

Using MySQL DATE_ADD function when creating calculated fields in Adhoc

Explanation :

By default, the DATE_ADD function in MySQL function is not available under the Adhoc View calculated fields lists of function

Resolution : 

In order to make use of the DATE_ADD function, we need to add the following in applicationContext-semanticLayer.xml file under property "functionTemplates" inside bean with id "defaultSQLGenerator"

<entry key="addDay">
    <value>"DATE_ADD(" + sqlArgs[0] + ", INTERVAL " + sqlArgs[1] + " DAY)"</value>
</entry>
<entry key="addMonth">
    <value>"DATE_ADD(" + sqlArgs[0] + ", INTERVAL " + sqlArgs[1] + " MONTH)"</value>
</entry>

For displaying the newly created custom function in the Adhoc Calculated Field Function List add the following two function definitions under util:list id="operatorDefinitionList" in file WEB-INFapplicationContext-el-operators.xml -

For adding the addDay custom Function:

<bean parent="functionDef">
    <property name="name" value="addDay"/>
    <property name="javaType" value="java.sql.Timestamp"/>
    <property name="argumentTypes">
    <list>
        <value>#{ objectTypeMapper.checkType('Timestamp') }</value>
        <value>#{ objectTypeMapper.checkType('String') }</value>
    </list>
    </property>
    <property name="properties">
        <map>
            <!-- show in calc field dialog -->
            <entry key="inAvailableFunctions" value="true"/>
            <!-- always run in SQL -->
            <entry key="alwaysInSQL" value="true"/>
        </map>
    </property>
    <property name="inMemory" value="false"/>
</bean>

For adding the addMonth custom Function:

<bean parent="functionDef">
    <property name="name" value="addMonth"/>
    <property name="javaType" value="java.sql.Timestamp"/>
    <property name="argumentTypes">
        <list>
            <value>#{ objectTypeMapper.checkType('Timestamp') }</value>
            <value>#{ objectTypeMapper.checkType('String') }</value>
        </list>
    </property>
    <property name="properties">
        <map>
            <!-- show in calc field dialog -->
            <entry key="inAvailableFunctions" value="true"/>
            <!-- always run in SQL -->
            <entry key="alwaysInSQL" value="true"/>
        </map>
    </property>
    <property name="inMemory" value="false"/>
</bean>

Save the files applicationContext-semanticLayer.xml and applicationContext-el-operators.xml and restart the server.

Now in Adhoc when creating calculated field use expression like below:

addMonth("date_field", '4')

Note 1: date_field is the name of a field in the database table

Note 2: For each different unit of time we need to add new functions. 

For example, to use DATE_ADD with WEEK interval we also need to add addWeek function:

<entry key="addWeek"> 
    <value>"DATE_ADD(" + sqlArgs[0] + ", INTERVAL " + sqlArgs[1] + " WEEK)"</value> 
</entry>

Similary, we have to add the function definition "addWeek" under util:list id="operatorDefinitionList" in file WEB-INFapplicationContext-el-operators.xml  file as stated above.