I would like to know what the common practice is for dealing with the interaction of timezones with timestamps in the OpenSIPS database.
I think it is certainly clear that storing timestamps as local time is a very bad idea in locales with daylight savings time. You can end up with ambiguous times and durations! For example, if your database shows a call that started at 1:30 and ended at 2:30 on the night that daylight savings time ended (at 2:00) then it is impossible to tell whether the call lasted one hour or two hours (it might have started at 1:30 daylight time and extended 2 hours until 2:30 standard time, or it might have started at 1:30 standard time and gone for one hour until 2:30 standard time). How will you rate that call? Even for timestamps that are not ambiguous, timestamps arithmetic with daylight savings time accounted for is still a nightmare. My feeling is that even in locales without daylight savings time, it's cleaner and more robust to store all timestamps in the database at UTC, especially if you have interconnected systems locales in different timezones. You can of course convert everything to local time as needed when you extract it. On the other hand, OpenSIPS uses local time in database timestamps. The functions (in db_ut.c) shared by all databases supported by OpenSIPS that format and parse timestamps do so in local time. One option for using UTC in the database is to run OpenSIPS itself under UTC (set the TZ environment variable to UTC in OpenSIPS's environment). This might have some unexpected effects though, for example if the script cares about local time (local time will equal UTC). Another option is to change the OpenSIPS database utility functions to convert timestamps for the database as UTC. (unfortunately there only exists a portable API in C to do this in one direction, not in the other.) How do you handle the timezone problem in the database? Options: - Ignore the problem, billing records might be incorrect by one hour during daylight savings time transitions - Run opensips with TZ=UTC - Run the whole host machine under UTC timezone. - something else? -Phil _______________________________________________ Users mailing list [email protected] http://lists.opensips.org/cgi-bin/mailman/listinfo/users
