Time Zone
Hi, We have a requirement of handling multiple timezones in Informix. We would like to know, does Informix supports automatic timezone handling using $TZ {$TZ is a Env. Variable in Unix for setting the Time Zone}. Or is there any other method of handling multiple timezones in Informix. For e.g., A datetime has been entered in a database at Mumbai ($TZ = IST-5:30). This datetime when retrieved from the same database in say Malaysia($TZ = EAT-8) should be displayed in Local Malaysian time and not in Mumbai Time. Regards, Basaiah Basaiah Hiremath Infosys Technologies Ltd. Pune
BASAIAH wrote: > > Hi, > > We have a requirement of handling multiple timezones in Informix. > > We would like to know, does Informix supports automatic timezone handling > using $TZ {$TZ is a Env. Variable in Unix for setting the Time Zone}. Or is > there any other method of handling multiple timezones in Informix. > > For e.g., > A datetime has been entered in a database at Mumbai ($TZ = IST-5:30). > This datetime when retrieved from the same database in say Malaysia($TZ = > EAT-8) should be displayed in Local Malaysian time and not in Mumbai Time. No, unfortunately Informix, and every other RDBMS of which I am aware, stores local time always not UST so conversion is difficult as you never know in what zone the data was entered. Global databases must keep track themselves or convert from local time to UST before inserting datetimes. Informix does recognize TZ in the environment with which the engine was started and always returns values in that timezone. ESQL/C and 4GL programs that use the CURRENT built-in or function call will user the TZ in their own environment. On thing you CAN do is to ALWAYS use the server's version of time, avoiding using application generated CURRENT values, and setup an INTERVAL you can use to convert DATETIME values from "server time" to local time. You then say in your code: EXEC SQL SELECT CURRENT YEAR TO SECOND INTO :my_current FROM systables WHERE tabid = 99; instead of: my_current = CURRENT year to second; Art S. Kagel
Basaih, Unfortunately this is not possible, not only with Informix but anyother RDBMS either. We have investigated this and the engine picks up and remebers the TZ the engine had been started with. The clients pickup the TZ from their own environments. A requrest could you please direct queries like this to me too.... Regards Bomi BASAIAH wrote: > Hi, > > We have a requirement of handling multiple timezones in Informix. > > We would like to know, does Informix supports automatic timezone handling > using $TZ {$TZ is a Env. Variable in Unix for setting the Time Zone}. Or is > there any other method of handling multiple timezones in Informix. > > For e.g., > A datetime has been entered in a database at Mumbai ($TZ = IST-5:30). > This datetime when retrieved from the same database in say Malaysia($TZ = > EAT-8) should be displayed in Local Malaysian time and not in Mumbai Time. > > Regards, > > Basaiah > > Basaiah Hiremath > Infosys Technologies Ltd. > Pune
Basaih, Unfortunately this is not possible, not only with Informix but anyother RDBMS either. We have investigated this and the engine picks up and remebers the TZ the engine had been started with. The clients pickup the TZ from their own environments. A requrest could you please direct queries like this to me too.... Regards Bomi BASAIAH wrote: > Hi, > > We have a requirement of handling multiple timezones in Informix. > > We would like to know, does Informix supports automatic timezone handling > using $TZ {$TZ is a Env. Variable in Unix for setting the Time Zone}. Or is > there any other method of handling multiple timezones in Informix. > > For e.g., > A datetime has been entered in a database at Mumbai ($TZ = IST-5:30). > This datetime when retrieved from the same database in say Malaysia($TZ = > EAT-8) should be displayed in Local Malaysian time and not in Mumbai Time. > > Regards, > > Basaiah > > Basaiah Hiremath > Infosys Technologies Ltd. > Pune