将精确的音频位置存储为双精度秒数是否安全？
我需要在数据库（即 SQLite）中存储精确的音频位置。我可以将帧位置（样本偏移/通道）存储为整数，但这在某些文件转换的情况下会导致额外的数据维护。
所以我正在考虑将位置存储为以秒为单位的 8 字节实数值，即双精度值，因此作为 SQLite 中的 REAL 值。这使得数据库结构更加一致。
但是，给定最大采样率为 192kHz，双精度是否足以使我在将该值乘以采样率时始终能够恢复准确的帧位置？
是否存在某个最大位置，超过该位置可能会出现错误？这个最大位置是多少？
PS：这与SQLite REAL有关，也与C和Java double类型有关，它们可能在各个阶段保存位置值。
更新：
由于现在讨论的重点是与转换和舍入相关的风险，因此我计划使用以下 C 方法：
// Given these types:
int samplerate;
long long framepos;
double position;
// First compute the position in seconds from the framepos:
position = (double) framepos / samplerate;
// Now store the position in an SQLite REAL column, and retrieve it later
// Then compute the framepos back from position, with rounding:
framepos = position * samplerate + 0.5;
这安全且对称吗？
I need to store an exact audio position in a database, namely SQLite. I could store the frame position (sample offset / channels) as an integer, but this would cause extra data maintenance in case of certain file conversions.
So I'm thinking about storing the position as an 8 byte real value in seconds, that is a double, and so as a REAL in SQLite. That makes the database structure more consistent.
But, given a maximum samplerate of 192kHz, is the double precision sufficient so that I can always recover the exact frame position when multiplying the value by the samplerate?
Is there a certain maximum position above which an error may occur? What is this maximum position?
PS: this is about SQLite REAL, but also about the C and Java double type which may hold the position value at various stages.
Update:
Since the discussions now focus on the risks related to conversion and rounding, here's the C method that I'm planning to use:
// Given these types:
int samplerate;
long long framepos;
double position;
// First compute the position in seconds from the framepos:
position = (double) framepos / samplerate;
// Now store the position in an SQLite REAL column, and retrieve it later
// Then compute the framepos back from position, with rounding:
framepos = position * samplerate + 0.5;
Is this safe and symmetrical?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
双精度数有 51 位精度。根据指数部分，其中一些位将表示整数（在您的情况下为秒），其他位表示秒的分数。
在 48 kbit 时，至少需要 16 位才能获得足够的亚秒精度（如果舍入不是最佳，则需要更多位）。剩下 35 位代表秒，这将跨越一千年多一点。
因此，即使您需要在亚秒内额外增加一两位以防止舍入，并且即使 SQL 在将其转换为十进制并在这里或那里返回时丢失一两位精度，您也不会丢失样本精度用你的双精度数。确保你的舍入工作正确 - C 在转换为整数时往往总是向下舍入，因此即使是无限小的转换错误也可能会让你损失 1。
A double has 51 bits worth of precision. Depending on the exponent part, some of these bits will represent whole numbers (seconds in your case), the others fractions of seconds.
At 48 kilobits, a minimum of 16 bits is required to get the sub-second precise enough (more if rounding is not optimal). That leaves 35 bits for the seconds, which will span just over a thousand years.
So even if you need an extra bit or two for the sub-second to guard against rounding, and even if SQL loses a bit or two of precision converting it to decimal and back here and there, you aren't anywhere near losing sample precision with your double precision number. Make sure your rounding works correctly - C tends to always round down on convert to integer, so even an infintessimaly small conversion error could throw you off by 1.
我将其存储为表示微秒（大约 2**20）的（64 位）整数。这避免了浮点硬件/软件，很容易被所有人理解，并为您提供 0..2**44 秒的范围，即 55000 年多一点。
作为替代方案，使用可读的固定精度十进制表示形式（20 位数字就足够了）。带前导零右对齐。无论如何，与数据库访问相比，转换成本可以忽略不计。
这些选项的优点之一是任何数据库都会知道如何对它们进行排序，这对于浮点值来说不一定很明显。
I would store it as a (64-bit) integer representing microseconds (approx 2**20). This avoids floating point hardware/software, is readily understood by all, and gives you a range of 0..2**44 seconds which is a little over 55 thousand years.
As an alternative, use a readable fixed precision decimal representation (20 digits should be enough). Right-justified with leading zeros. The cost of conversion is negligible compared to DB accesses anyway.
One advantage of these options is that any database will trivially know how to order them, not necessarily obvious for floating point values.
正如 Matthias Wandel 的回答所解释的那样，可能没有什么可担心的。 OTOH 通过使用整数，无论大小如何，您都会获得固定的精度，这可能是有用的。
假设使用 64 位整数，并将时间存储为微秒。这为您提供了 1 MHz 的等效采样精度和近 300000 年的范围（如果我的快速计算正确的话）。
编辑 即使考虑到时间戳 *sample_rate 需要适合 64 位整数，您仍然有 1.5 年的范围 (2**63/1e6/3600/24/365 /192e3)，假设最大采样率为 192kHz。
As the answer by Matthias Wandel explains, there's probably nothing to worry about. OTOH by using integers you would get fixed precision regardless of the magnitude which might be useful.
Say, use a 64-bit integer, and store the time as microseconds. That gives you an equivalent sampling precision of 1 MHz and a range of almost 300000 years (if my quick calculation is correct).
Edit Even when taking into account the need for the timestamp * sample_rate to fit into a 64-bit integer, you still have a range of 1.5 years (2**63/1e6/3600/24/365/192e3), assuming a max sample rate of 192kHz.