Python中如何处理SQLite浮点数精度问题及时间戳查询？

问题原因 #

SQLite和Python中的浮点数都采用64位双精度存储，但部分十进制小数无法精确转换为二进制浮点数，比如你插入的1669836415.8800698。存储和读取过程中，这个值会被近似为最接近的二进制浮点数，导致读取后的值与原始值存在微小差异，直接用等值查询自然匹配不到目标行。

解决方案 #

1. 用范围查询替代等值匹配 #

查询时给浮点数设置一个极小的误差范围，允许微小的精度偏差：

import sqlalchemy as sa
import pandas as pd

def main():
    file_path = '/path/to/sqlite.db'
    conn_str = f'sqlite:///{file_path}'
    engine = sa.create_engine(conn_str)

    target_ts = 1669836415.8800698
    # 设置误差范围，比如1e-6（百万分之一），适配微秒级精度
    query = '''
        SELECT * FROM test 
        WHERE job_id = ? 
        AND modified_on BETWEEN ? - 1e-6 AND ? + 1e-6
    '''
    params = ("bc011fcd6c8e40069dd7c7f2fdf92952", target_ts, target_ts)
    data = pd.read_sql_query(query, con=engine, params=params)
    print(data['modified_on'].iloc[0])

if __name__ == '__main__':
    main()

2. 改用整数/字符串存储时间戳（推荐） #

彻底规避浮点数精度问题，将时间戳转换为整数或字符串存储：

• 整数存储：如果是毫秒/微秒级时间戳，乘以对应倍数转成整数（比如微秒级就乘以1e6），存储为INTEGER类型。查询时直接用整数匹配，完全无精度损失。
  示例：插入时存1669836415880069（对应1669836415.880069秒），查询时直接用该整数筛选。
• 字符串存储：将时间戳格式化为ISO标准字符串（如"2022-11-30T12:06:55.880069"），存储为TEXT类型，查询时用字符串等值匹配即可。

3. 读取后处理浮点数用于查询 #

如果必须用浮点数查询，可对读取后的浮点数进行四舍五入，保留到你需要的精度后再执行查询：

import sqlalchemy as sa
import pandas as pd

def main():
    file_path = '/path/to/sqlite.db'
    conn_str = f'sqlite:///{file_path}'
    engine = sa.create_engine(conn_str)

    # 先读取数据
    query = 'select * from test WHERE job_id = "bc011fcd6c8e40069dd7c7f2fdf92952"'
    data = pd.read_sql_query(query, con=engine)
    ts = data['modified_on'].iloc[0]
    
    # 四舍五入到6位小数（微秒级）
    rounded_ts = round(ts, 6)
    
    # 用处理后的值查询
    query = 'SELECT * FROM test WHERE job_id = ? AND modified_on = ?'
    params = ("bc011fcd6c8e40069dd7c7f2fdf92952", rounded_ts)
    data = pd.read_sql_query(query, con=engine, params=params)
    print(data)

if __name__ == '__main__':
    main()

关于四舍五入的疑问 #

不是必须固定四舍五入到6位小数，需根据你的时间戳精度需求决定：

• 如果是微秒级精度（小数点后6位），四舍五入到6位足够；
• 如果需要更高精度，可尝试保留7位，但要注意64位双精度浮点数的有效位数约为15-17位，超过后仍可能存在精度损失。

最稳妥的方案还是改用整数或字符串存储时间戳，从根源上避免浮点数精度问题。

内容的提问来源于stack exchange，提问作者Mecova