Passing timezone DBfunction

Database Support
, , , ,
#1

Hello Helical Insight,

Is it possible to create a dbfunction for mysql so that we can pass timezone information and accordingly the data will get changed?

Thank You,
Vema.

#2

Hello Vema,

Yes, it is always possible to add more functions in the database functions xml file. At the location “..\hi\hi-repository\System\Admin\SqlFunctions” we are having XML file for every DB.

In mysql.xml file add a dbfunction mentioned below which will allow to pass data in a specific timezone or add remove hours from the value. Obviously for such things to work that respective DB should support that as well.

<function group="dateTime" returns="dateTime">
        <key>sql.dateTime.zoneconvesion</key>
        <value>ZONECONVERSION</value>
        <signature><![CDATA[CONVERT_TZ(${datetime},${from_tz},${to_tz})]]></signature>
        <parameters>
            <parameter column="true" name="datetime" defaultValue="'2008-05-15 12:00:00'"/>
            <parameter column="true" name="from_tz" defaultValue="'+00:00'"/>
            <parameter column="true" name="to_tz" defaultValue="'+13:00'"/>
        </parameters>
        <description>This function is used to add / substract time from date time. Example: CONVERT_TZ('2008-05-15 12:00:00','+00:00','+13:00') result: 2008-05-16 01:00:00</description> 
    </function>

Thank You,
Helical Insight.