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
More suggestions? Pro or cons opinions?
I'd just save one timestamp, ie TAI64 or as java does miliseconds since epoch, in a single, new field. saving the timestamp in two fields seems messy.
miliseconds since epoch is probably preferable, since it can be converted to human readable dates by the database server.
then have a flag that may be enabled, which additionally saves the time in the current format (with less precision) -- for backward compatability.
but a change like this i'd save for a 5.0 release.
kind regards Thilo
Cheers, Daniel
Hi,
On 04/29/2013 11:47 AM, Thilo Bangert wrote:
I'd just save one timestamp, ie TAI64 or as java does miliseconds since epoch, in a single, new field. saving the timestamp in two fields seems messy.
Agreed.
miliseconds since epoch is probably preferable, since it can be converted to human readable dates by the database server.
What I like about the DECIMAL approach is that it's (at least in MySQL) usable with from_unixtime functions, in case you need quick access to human readable format. Up until 5.1 it only shows seconds precision in that case, not sure about high resolution precision in 5.6 where timestamp seems to support microseconds.
Also not sure about compatibility with other DB engines.
Andreas
Hello,
On 4/29/13 2:15 PM, Andreas Granig wrote:
Hi,
On 04/29/2013 11:47 AM, Thilo Bangert wrote:
I'd just save one timestamp, ie TAI64 or as java does miliseconds since epoch, in a single, new field. saving the timestamp in two fields seems messy.
Agreed.
the option to get current format is a must, there are many tools depending on how acc records are written now. The idea is to add a mechanism that allows people to store other formats that may prefer. Moving from current datetime format to a single different forma value is not a good decision.
miliseconds since epoch is probably preferable, since it can be converted to human readable dates by the database server.
What I like about the DECIMAL approach is that it's (at least in MySQL) usable with from_unixtime functions, in case you need quick access to human readable format. Up until 5.1 it only shows seconds precision in that case, not sure about high resolution precision in 5.6 where timestamp seems to support microseconds.
Also not sure about compatibility with other DB engines.
It doesn't look at all as the only format to use. But it can be an option to store, having its own good benefits, however it is now spread considering existing stable os deployments. Again, it may require going through other modules, the db connectors.
Cheers, Daniel
Hello,
any more comments on time storage for acc records?
Obviously, it has to be done via something that is configurable and extensible.
If no more comments, I will go ahead and develop soon a configurable framework for seconds and seconds, microseconds, then others can come and add more.
Cheers, Daniel
On 5/1/13 11:08 AM, Daniel-Constantin Mierla wrote:
Hello,
On 4/29/13 2:15 PM, Andreas Granig wrote:
Hi,
On 04/29/2013 11:47 AM, Thilo Bangert wrote:
I'd just save one timestamp, ie TAI64 or as java does miliseconds since epoch, in a single, new field. saving the timestamp in two fields seems messy.
Agreed.
the option to get current format is a must, there are many tools depending on how acc records are written now. The idea is to add a mechanism that allows people to store other formats that may prefer. Moving from current datetime format to a single different forma value is not a good decision.
miliseconds since epoch is probably preferable, since it can be converted to human readable dates by the database server.
What I like about the DECIMAL approach is that it's (at least in MySQL) usable with from_unixtime functions, in case you need quick access to human readable format. Up until 5.1 it only shows seconds precision in that case, not sure about high resolution precision in 5.6 where timestamp seems to support microseconds.
Also not sure about compatibility with other DB engines.
It doesn't look at all as the only format to use. But it can be an option to store, having its own good benefits, however it is now spread considering existing stable os deployments. Again, it may require going through other modules, the db connectors.
Cheers, Daniel
For the records, this has been implemented in master branch - a matter of time_mode parameter for acc, time value can be stored in various formats.
Cheers, Daniel
On 5/10/13 9:50 AM, Daniel-Constantin Mierla wrote:
Hello,
any more comments on time storage for acc records?
Obviously, it has to be done via something that is configurable and extensible.
If no more comments, I will go ahead and develop soon a configurable framework for seconds and seconds, microseconds, then others can come and add more.
Cheers, Daniel
On 5/1/13 11:08 AM, Daniel-Constantin Mierla wrote:
Hello,
On 4/29/13 2:15 PM, Andreas Granig wrote:
Hi,
On 04/29/2013 11:47 AM, Thilo Bangert wrote:
I'd just save one timestamp, ie TAI64 or as java does miliseconds since epoch, in a single, new field. saving the timestamp in two fields seems messy.
Agreed.
the option to get current format is a must, there are many tools depending on how acc records are written now. The idea is to add a mechanism that allows people to store other formats that may prefer. Moving from current datetime format to a single different forma value is not a good decision.
miliseconds since epoch is probably preferable, since it can be converted to human readable dates by the database server.
What I like about the DECIMAL approach is that it's (at least in MySQL) usable with from_unixtime functions, in case you need quick access to human readable format. Up until 5.1 it only shows seconds precision in that case, not sure about high resolution precision in 5.6 where timestamp seems to support microseconds.
Also not sure about compatibility with other DB engines.
It doesn't look at all as the only format to use. But it can be an option to store, having its own good benefits, however it is now spread considering existing stable os deployments. Again, it may require going through other modules, the db connectors.
Cheers, Daniel
Hello,
On 4/29/13 11:47 AM, Thilo Bangert wrote:
More suggestions? Pro or cons opinions?
I'd just save one timestamp, ie TAI64 or as java does miliseconds since epoch, in a single, new field. saving the timestamp in two fields seems messy.
it can be one field only, it is a matter of module parameters.
miliseconds since epoch is probably preferable, since it can be converted to human readable dates by the database server.
miliseconds is cutting from the precision that is get inside. Also have in mind that there are many database connectors, not only mysql.
then have a flag that may be enabled, which additionally saves the time in the current format (with less precision) -- for backward compatability.
but a change like this i'd save for a 5.0 release.
the plan is to get it for 4.1.0, 5.0.0 looks a bit far away at this moment.
Cheers, Daniel
kind regards Thilo
Cheers, Daniel
SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list sr-users@lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users
On Monday 29 April 2013 11:05:36 Daniel-Constantin Mierla wrote:
Here are some suggestions presented so far.
- 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.
- 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
Hi,
On 04/29/2013 01:42 PM, Alex Hermann wrote:
On Monday 29 April 2013 11:05:36 Daniel-Constantin Mierla wrote:
- 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.
Just for clarification, we're using DECIMAL(13,3) with the patch on the tracker, not DOUBLE. What's missing in this patch is the table definition, so it needs to be updated in any case.
Andreas
Hello,
On 4/29/13 1:42 PM, Alex Hermann wrote:
On Monday 29 April 2013 11:05:36 Daniel-Constantin Mierla wrote:
Here are some suggestions presented so far.
- 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.
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.
- 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.
Probably easier that dealing with formatting back from datetime and take care of timezone and daylight saving time.
Any other suggestions?
- Use native mili/microseconds support for DATETIME or TIMESTAMP in the
database. At least MariaBD and PostgreSQL support this.
There are other db_* connectors that have to handle nicely the new addition.
- 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
At this moment is datetime.
Cheers, Daniel
-
Alex Hermann -
Andreas Granig -
Daniel-Constantin Mierla -
Thilo Bangert