29 Apr
2013
29 Apr
'13
11:05 a.m.
Hello,
there were old and recent discussions about the representation of time
in accounting records. At this moment the acc module stores the unix
timestamp as datetime value.
In some countries is required to store more accuracy, beyond the
seconds. Also, the datetime gives some troubles with converting back to
unix timestamp, specifically with time zones and daylight saving times.
I want to open a discussion that is visible for all users and devs,
being something affecting probably everyone, input from anyone that is
interested being relevant to select the right approach.
Here are some suggestions presented so far.
1) store seconds.miliseconds as double - there is a patch (which
probably needs some tunings itself) on tracker. The precision is shifted
from seconds to milliseconds. Internally computed from microseconds from
timeval structure. The other modules might need updates (iirc dbtext has
only 2 decimals precisions for double, while miliseconds will require at
least three -- could be the case for flatstore or other modules).
Advantage is the computing of duration directly by subtraction of two
values.
2) store seconds and microseconds as two separate values. Should be no
issues with existing modules. Even more accuracy than above, no issues
with other modules, but will require to use two columns (thus four
values to compute the duration)
Any other suggestions?
Personally, at this moment, I will go with 2), but that might not meet
everyone's needs. So a solution can be to make it configurable, as a
bitmask of what time representation to store.
Say, there will be a new parameter timestamp_mode for acc module:
- bit one set - store seconds timestamp as for now (default)
- bit two set - store seconds and microseconds in separate integer columns
- bit three set - store seconds.miliseconds as double value in one column
I could add the parameter and tune acc for bit one and two. For bit
three a deeper review is needed for other modules, probably the
developer that submitted to the tracker can do it.
More suggestions? Pro or cons opinions?
Cheers,
Daniel
--
Daniel-Constantin Mierla - http://www.asipto.com
http://twitter.com/#!/miconda - http://www.linkedin.com/in/miconda
Kamailio Advanced Training, San Francisco, USA - June 24016, 2013
* http://asipto.com/u/katu *
29 Apr
29 Apr
1:42 p.m.
New subject: [SR-Users] rfc: accounting records time details
On Monday 29 April 2013 11:05:36 Daniel-Constantin Mierla wrote:
Here are some suggestions presented so far.
1) store seconds.miliseconds as double - there is a patch (which
Please do not use floating point respresentations for values that will be used
in accounting. Floating point is imprecise. As the time related columns will
most probably be used for billing, the values should be exact. In SQL this
means using the DECIMAL or NUMERIC column type.
2) store seconds and microseconds as two separate
values. Should be no
issues with existing modules. Even more accuracy than above, no issues
with other modules, but will require to use two columns (thus four
values to compute the duration)
Difficult to use in calculations.
Any other suggestions?
3) Use native mili/microseconds support for DATETIME or TIMESTAMP in the
database. At least MariaBD and PostgreSQL support this.
4) Store mili/microseconds since epoch in a BIGINT column.
Say, there will be a new parameter timestamp_mode for
acc module:
- bit one set - store seconds timestamp as for now (default)
- bit two set - store seconds and microseconds in separate integer columns
- bit three set - store seconds.miliseconds as double value in one column
- bit
three set - store seconds.miliseconds as DECIMAL value in one column
- bit four set - add mili/microseconds to DATETIME (only valid when bit 1 is
set too)
- bit five set - store mili/microseconds since epoch as BIGINT value in one
column
Alternatively, 2 settings can be used, one for storage format and one to
choose the precision/resolution. This provides the most flexibility for the
user.
timestamp_format:
datetime (TIMESTAMP or DATETIME)
epoch ((BIG)INT or DECIMAL, depending on resolution)
split_epoch (2x INT)
timestamp_resolution: seconds, miliseconds, microseconds
Default would be the current situation: datetime + seconds
--
Greetings,
Alex Hermann
1 May
1 May
10:57 a.m.
New subject: [SR-Users] rfc: accounting records time details
Hello,
On 4/29/13 1:42 PM, Alex Hermann wrote:
Probably easier that dealing with formatting back from datetime and take
care of timezone and daylight saving time.
There are other db_* connectors that have to handle nicely the new addition.
At this moment is datetime.
Cheers,
Daniel
--
Daniel-Constantin Mierla - http://www.asipto.com
http://twitter.com/#!/miconda - http://www.linkedin.com/in/miconda
Kamailio Advanced Training, San Francisco, USA - June 24-27, 2013
* http://asipto.com/u/katu *
On Monday 29 April 2013 11:05:36 Daniel-Constantin
Mierla wrote:
There are non-sql database
connectors, so it has to be something working
for everything. That's why I proposed variants, even possible to store
couple of formats at the same time.
Here are some suggestions presented so far.
1) store seconds.miliseconds as double - there is a patch (which
Please do not use
floating point respresentations for values that will be used
in accounting. Floating point is imprecise. As the time related columns will
most probably be used for billing, the values should be exact. In SQL this
means using the DECIMAL or NUMERIC column type. 2) store
seconds and microseconds as two separate values. Should be no
issues with existing modules. Even more accuracy than above, no issues
with other modules, but will require to use two columns (thus four
values to compute the duration)
Difficult to use in calculations. Any other
suggestions?
3) Use native mili/microseconds support for DATETIME or TIMESTAMP in
the
database. At least MariaBD and PostgreSQL support this.
4) Store mili/microseconds since epoch in a BIGINT column.
Can be added if anyone writes the code for the option and checks/updates
the db connectors for compatibility.
Say, there
will be a new parameter timestamp_mode for acc module:
- bit one set - store seconds timestamp as for now (default)
- bit two set - store seconds and microseconds in separate integer columns
- bit three set - store seconds.miliseconds as double value in one column
- bit
three set - store seconds.miliseconds as DECIMAL value in one column
- bit four set - add mili/microseconds to DATETIME (only valid when bit 1 is
set too)
- bit five set - store mili/microseconds since epoch as BIGINT value in one
column
Alternatively, 2 settings can be used, one for storage format and one to
choose the precision/resolution. This provides the most flexibility for the
user.
timestamp_format:
datetime (TIMESTAMP or DATETIME)
epoch ((BIG)INT or DECIMAL, depending on resolution)
split_epoch (2x INT)
timestamp_resolution: seconds, miliseconds, microseconds
Default would be the current situation: datetime + seconds
4013
days inactive
4015
days old
2 comments
2 participants
participants (2)
-
Alex Hermann -
Daniel-Constantin Mierla