In TIBCO Data Virtualization (TDV), when using the TDV built-in TZCONVERTOR function with Oracle DB, the function is observed to get pushed down to the DB. 

 Sample query -   SELECT TZCONVERTOR (parse_timestamp('2023-03-29 00:30:00.000', 'yyyy-mm-dd hh24:mi:ss.ff3'), 'GMT', 'MST') a0001 FROM /shared/Oracle19c/TESTUSER/TT_03_120956_L

No Push reason in the SELECT node of the Query plan - 

Screenshot for reference - 


This article provides details how to push down TZCOVERTOR function to Oracle DB in TDV. 

In this scenario, we have taken the example of Oracle19c adapter to push down the TZCONVERTOR function. Follow the below steps -

(1) Take a backup of the [TDV_Server_dir]\apps\dlm\cis_ds_oracle\conf\oracle.capabilities file. 
(2) Update the original file with the following -

 TZCONVERTOR(@timestamp,~string,~string): cast(from_tz($1, $2) at time zone $3 as TIMESTAMP)

Oracle documentation on FROM_TZ - https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/FROM_TZ.html#GUID-84384FF7-6462-480C-BC40-60087016857B

Here is an example of the Oracle capabilities file for reference - 



(3) Restart TDV Server for the changes to take effect.
(4) Run the query. The function TZCONVERTOR is seen to be pushed down to Oracle DB in the FETCH node - 

NOTE :

•  The oracle.capabilities file may get overridden by hotfixes and service packs. Make sure to follow the above steps again after every upgrade until it has been implemented in the product.  
•  It is important to use the timezones supported by both the TDV and the Oracle DB when using TZCONVERTOR function e.g. GMT, MST, etc. for the query to return result or else the user may run into  'Cause: ORA-01857: not a valid time zone' error. Here is the list of timezones supported by the TDV and the Oracle DB:
• Oracle: https://docs.oracle.com/cd/B13866_04/webconf.904/b10877/timezone.htm (You would need to refer to the official Oracle documentation for any specific Oracle DB timezone support)
• TDV:  https://docs.tibco.com/pub/tdv/8.8.0/doc/html/en-US/StudioHelp/#Reference/Time_Zones.html#appendix_c_time_zones_3602597497_916776?TocPath=Reference%2520Guide%257C_____12
•  The above changes will be applied to all the TDV adapters. If the change is only required for a specific Oracle adapter like Oracle 19c thin adapter, then update the oracle_19c_thin_driver.oracle.capabilities file instead of oracle.capabilities file in step(2).