[OpenSIPS-Users] Best practices inquiry: database timezone

[Phil Vandry]

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