[OpenSIPS-Users] Best practices inquiry: database timezone

[Phil Vandry]

On Tue, Mar 31, 2009 at 05:35:48PM +0300, Vasil Kolev wrote:
> Haven't checked the module which does this, but using the time from
> opensips is not a good idea anyway, I've always use the time in the
> database. So, mostly, insert into call(start_time) values (NOW()), and
> then update call set end_time=NOW().... This way you also know that
> you're talking to one clock (as you can have more than one instance of
> opensips running).

There is some risk with this approach because you are going to log
the time that the database processed the query, not the time of the
call. The opensips "acc" module accounting output trigger fires right
after the SIP final reply has gone out. If it takes some time for
the database to run the query (for example if the database connection
is dead and needs to be closed and reopened or if there is a network
timeout and rety or if there is lock contention on the database)
then the accounting record will indicate a time somewhat later than
when the SIP request actually happened.

Nevertheless, I am thinking of following your suggestion combined
with Alex Hermann's suggestion:

- ignore the time field set by the "acc" module

- create a new column with type Mysql type "TIMESTAMP DEFAULT
CURRENT_TIMESTAMP". The acc module will not know about this column
so it will get filled in with the default value which is the database
server's time.

- Our mysql server already runs with system timezone UTC (command line
argument --timezone=UTC) so the autoconversion associated with the
TIMEZONE data type is deafeated and our reporting and rating tools
will read the time in UTC, which is that they already expect.

But I still think it's a hack :-(

-Phil