Best practices inquiry: database timezone

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

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

_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: Best practices inquiry: database timezone

Alex Hermann
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.
--
Alex Hermann


_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: Best practices inquiry: database timezone

Vasil Kolev
In reply to this post by Phil Vandry
В 22:55 -0400 на 30.03.2009 (пн), 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?
>

In my experience (which is mostly postgres) the local type "timestamp
with timezone" is what you need - it records the date in UTC with the
timezone it was in, so in the end you always have the UTC time and can
go from that. Or you can go to pure UTC, most unixes keep time in it
anyway (and the timezone is just an environment variable that gets
changed, to display the time to the user :) ).




--
Regards,
Vasil Kolev
Attractel NV
dCAP #1324, LPIC2


_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: Best practices inquiry: database timezone

Bogdan-Andrei Iancu
Hi,

I have to agree here - using UTC for DB is the best practice, IMO. This
ensure the consistency across your platform and also easy to sync data
between your platform and external sources (like CDR mediation with the
PSTN providers).

Regards,
Bogdan


Vasil Kolev wrote:

> В 22:55 -0400 на 30.03.2009 (пн), 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?
>>
>>    
>
> In my experience (which is mostly postgres) the local type "timestamp
> with timezone" is what you need - it records the date in UTC with the
> timezone it was in, so in the end you always have the UTC time and can
> go from that. Or you can go to pure UTC, most unixes keep time in it
> anyway (and the timezone is just an environment variable that gets
> changed, to display the time to the user :) ).
>
>
>
>
>  


_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: Best practices inquiry: database timezone

Phil Vandry
In reply to this post by Vasil Kolev
On Tue, Mar 31, 2009 at 12:18:34PM +0300, Vasil Kolev wrote:
> In my experience (which is mostly postgres) the local type "timestamp
> with timezone" is what you need - it records the date in UTC with the
> timezone it was in, so in the end you always have the UTC time and can

That's a good idea, but OpenSIPS does not currently supply the
information to the database to make good use of that data type. It
converts its internal representation (time_t, which is UTC time as an
integer) to a string in the format "YYYY-mm-dd HH:MM:SS" in local time
and gives that string to the database.

-Phil

_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: Best practices inquiry: database timezone

Vasil Kolev
В 10:19 -0400 на 31.03.2009 (вт), Phil Vandry написа:

> On Tue, Mar 31, 2009 at 12:18:34PM +0300, Vasil Kolev wrote:
> > In my experience (which is mostly postgres) the local type "timestamp
> > with timezone" is what you need - it records the date in UTC with the
> > timezone it was in, so in the end you always have the UTC time and can
>
> That's a good idea, but OpenSIPS does not currently supply the
> information to the database to make good use of that data type. It
> converts its internal representation (time_t, which is UTC time as an
> integer) to a string in the format "YYYY-mm-dd HH:MM:SS" in local time
> and gives that string to the database.
>

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).


--
Regards,
Vasil Kolev
Attractel NV
dCAP #1324, LPIC2


_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: Best practices inquiry: database timezone

Phil Vandry
In reply to this post by Alex Hermann
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

_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: Best practices inquiry: database timezone

Phil Vandry
In reply to this post by Vasil Kolev
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

_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users