Database for more than one time zone

Hello, my task is to design a system based on Informix standard engine and esql/C for travel agencies, spread from Germany to Britain. The system shall record time tables for trains, coaches and planes. How can I achieve, that the database handles times interchangeable from user to user and from season to season? 1st example: A German user creates a time table. He records all times in his local time. Another German user must get the same times displayed from this table, because his workstation is located in the same timezone. A British user must get all times displayed shifted to one hour less, which is his local time, because his workstation is located in a different timezone. 2nd example: A time table is constructed from intervals. The journey starts on March 27th 1999 at 8 p.m. in Munich. First step is a ride on a coach to Calais, which lasts 9 hours. The database must calculate the arrival time as March 28th 1999 6 a.m., because meanwhile the timezone has switched over to daylight savings time. Many thanks to anybody who can give me a hint how to solve this.

Heinrich Wolf wrote: > > Hello, > > my task is to design a system based on Informix standard engine and > esql/C for travel agencies, spread from Germany to Britain. The system > shall record time tables for trains, coaches and planes. How can I > achieve, that the database handles times interchangeable from user to > user and from season to season? > > 1st example: > A German user creates a time table. He records all times in his local > time. Another German user must get the same times displayed from this > table, because his workstation is located in the same timezone. A > British user must get all times displayed shifted to one hour less, > which is his local time, because his workstation is located in a > different timezone. > > 2nd example: > A time table is constructed from intervals. The journey starts on March > 27th 1999 at 8 p.m. in Munich. First step is a ride on a coach to > Calais, which lasts 9 hours. The database must calculate the arrival > time as March 28th 1999 6 a.m., because meanwhile the timezone has > switched over to daylight savings time. > > Many thanks to anybody who can give me a hint how to solve this. Old problem, UNIX provides the solution. Convert all input times to GMT for storage and reconvert to a user's local time for display. This has the advantage that you can allow the user to select his own local time or the local time at the terminal location and you can print itineraries in any of originating, terminating or in-transit times. Since different travelers prefer one over the other, or may want to carry an in-transit itinerary but leave an originating itinerary home for the boss or spouse to use. This can be a powerful feature. Once you have the generic conversion routine written the rest is trivial. Art S. Kagel

Art S. Kagel schrieb: > > Heinrich Wolf wrote: > > > > Hello, > > > > my task is to design a system based on Informix standard engine and > > esql/C for travel agencies, spread from Germany to Britain. The system > > shall record time tables for trains, coaches and planes. How can I > > achieve, that the database handles times interchangeable from user to > > user and from season to season? > > ... > > Old problem, UNIX provides the solution. Convert all input times to > GMT for storage and reconvert to a user's local time for display. > This has the advantage that you can allow the user to select his own > local time or the local time at the terminal location and you can print > itineraries in any of originating, terminating or in-transit times. > Since different travelers prefer one over the other, or may want to > carry an in-transit itinerary but leave an originating itinerary home > for the boss or spouse to use. This can be a powerful feature. Once > you have the generic conversion routine written the rest is trivial. > > Art S. Kagel Thank you for your quick response! I know how to work with the C library functions localtime(), mktime() and with the data type time_t. But I wonder if there is really no solution using the SQL DateTime Type. So I should never use DateTime but long int. And in case I need the SQL DateTime Value Current, then I see, that the esql/C manual does not offer any direct conversion. It says instead, that I should print the value to a string. Then I can read the string with scanf() and make a time_t with mktime(). Very fine solution! ;-}

Heinrich Wolf wrote: > > Art S. Kagel schrieb: > > > > Heinrich Wolf wrote: > > > > > > Hello, > > > > > > my task is to design a system based on Informix standard engine and > > > esql/C for travel agencies, spread from Germany to Britain. The system > > > shall record time tables for trains, coaches and planes. How can I > > > achieve, that the database handles times interchangeable from user to > > > user and from season to season? > > > > ... > > > > Old problem, UNIX provides the solution. Convert all input times to > > GMT for storage and reconvert to a user's local time for display. > > This has the advantage that you can allow the user to select his own > > local time or the local time at the terminal location and you can print > > itineraries in any of originating, terminating or in-transit times. > > Since different travelers prefer one over the other, or may want to > > carry an in-transit itinerary but leave an originating itinerary home > > for the boss or spouse to use. This can be a powerful feature. Once > > you have the generic conversion routine written the rest is trivial. > > > > Art S. Kagel > > Thank you for your quick response! > > I know how to work with the C library functions localtime(), mktime() > and with the data type time_t. But I wonder if there is really no > solution using the SQL DateTime Type. So I should never use DateTime but > long int. And in case I need the SQL DateTime Value Current, then I see, > that the esql/C manual does not offer any direct conversion. It says > instead, that I should print the value to a string. Then I can read the > string with scanf() and make a time_t with mktime(). Very fine solution! > ;-} Get my date/datetime library which converts between UNIX, Informix and my own date and datetime formats by decoding the datetime's decimal representation directly. Then you can get current, convert to my datetime format, adjust to GMT and convert back to Informix datetime and store. The library, datefuncs, is available from the IIUG Software Repository in the package datefuncs.shar. Currently the library works for all dates since the calendar change in the 1700's until time_t runs out in 2037, I'm working on the upper bound now. Art S. Kagel