[OpenSIPS-Users] Best practices inquiry: database timezone

[Phil Vandry]

On Tue, Mar 31, 2009 at 08:44:48AM +0100, Alex Hermann wrote:
> On Tuesday 31 March 2009 04:55:17 Phil Vandry wrote:
> > How do you handle the timezone problem in the database? Options:
> When using MySQL, use a TIMESTAMP field instead of DATETIME.

Hmm, that seems flaky to me:

"TIMESTAMP values are converted from the current time zone to UTC for
storage, and converted back from UTC to the current time zone for
retrieval" ( http://dev.mysql.com/doc/refman/5.0/en/timestamp.html )

So OpenSIPS converts its internal value (time_t, so UTC) to a local
time string, then the database converts it back to UTC for storage.
But OpenSIPS does not issue any "SET time_zone = ..." command to set
the Mysql timezone so you had better hope that the Mysql server's
system time zone is the same as the OpenSIPS timezone! This will not
generally be a problem if the two servers are closeby or colocated
but it still seems like a messy (and totally unnecesary) double
conversion.

I would much rather if the time was UTC over the wire between
OpenSIPS and the database.

-Phil