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

    Datasource server timezone

    nsabina
    nsabina Oct 6, 2011 5:43 PM

      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,

      Sabina

      • 2589 Views
      • Tags:
        • 1. Re: Datasource server timezone
          rareddy
          rareddy Oct 7, 2011 8:38 AM (in response to nsabina)

          Sabina,

           

          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"/>
          </translator>

           

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

           

          Ramesh..

            • Actions
          • 2. Re: Datasource server timezone
            nsabina
            nsabina Jan 17, 2012 2:37 AM (in response to rareddy)

            Thank you Ramesh

              • Actions