[OpenSIPS-Users] Best practices inquiry: database timezone

[Vasil Kolev]

В 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