2 Replies Latest reply on Jan 17, 2012 2:37 AM by Sabina Norderhaug

    Datasource server timezone

    Sabina Norderhaug Newbie

      As I learned there is a Teiid function to convert dates/timestamps between server and client timezones. For example:

      MODIFYTIMEZONE (timestamp, startTimeZone, endTimeZone)

      In case of multiple datasources, server timezone can be different.

      When I issue selects and need to specify time range, those values are defined in client's timezone in UI. Is there any existing practice or suggestion how to define/retrieve server timezones to be used in a function like above in where clause?


      Something like:


      select * from datasource1.table1 where timestamp between MODIFYTIMEZONE(TS1, clientTZ, serverTZ) and MODIFYTIMEZONE(TS2, clientTZ, serverTZ)


      Thank you,


        • 1. Re: Datasource server timezone
          Ramesh Reddy Master



          Yes. Have you thought about using the "DatabaseTimezone" translator property to define the source database location? See the override translator properties to define the timezone for each source, then time will be automatically converted to the server timezone. Check out the "Translator Overrides" tab on the vdb editor in the Teiid Designer to override the property.


          If u are using "multi-source" feature, then there may not be tooling for this, but you can accomplish same thing by editing the vdb.xml file in the .vdb file.


          Basically in your vdb.xml you extend the translator like



          <translator description="" type="oracle" name="oracle-central">
              <property value="GMT-6" name="DatabaseTimeZone"/>


          and use "oracle-central" as translator name on your source.