Flutter使用sqflite存储DateTime实现日期范围查询的咨询

• 当前存储使用的MM-DD-YYYY格式字符串不被SQLite内置日期函数识别，DATE()函数解析这类格式会返回空值，导致筛选条件完全失效。
• 查询代码中传入的参数[2021-01-01, 2021-01-31]在Dart语法中是算术减法表达式，计算结果为整数2019、1989，并非预期的日期字符串，无法匹配数据库存储的内容。
• 无匹配数据时SUM聚合函数会直接返回null，也就是当前遇到的查询结果异常。

sqflite本身不支持直接映射Dart的DateTime类型，但可通过标准化文本存储实现高效的日期范围查询，这也是sqflite官方推荐的存储方式：

存储端实现 #

• 不需要修改现有表结构，TEXT类型的日期列完全可以满足需求，无需额外换成整数类型存时间戳。
• 写入数据时，直接将Dart的DateTime对象转为ISO8601标准格式字符串存储，即yyyy-MM-dd HH:mm:ss格式，可直接调用DateTime.toIso8601String()方法生成。
• 这种格式的字符串字典序与真实时间顺序完全一致，不需要额外的格式转换即可直接做大小比较，查询效率最高。
• 前端展示时再通过格式化工具把存储的字符串转为MM-DD-YYYY的人类可读格式即可，存储和展示格式完全解耦。

示例存储代码：

// 新增支出记录时的写入逻辑
await db.insert(
  TABLE_EXPENSES,
  {
    COLUMN_NAME: '打车费',
    COLUMN_AMOUNT: 35,
    COLUMN_CATEGORY: '交通',
    // 直接转成标准ISO字符串存储，不要手动拼接MM-DD-YYYY格式
    COLUMN_DATETIME: DateTime.now().toIso8601String()
  },
);

查询端实现 #

• 不需要在SQL语句中嵌套多余的DATE()函数，直接对存储的标准化字符串做范围比较即可。
• 传参时必须把日期转为字符串类型传入，避免出现算术表达式被误解析为数字的问题。
• 其中DateFormat为intl官方格式化组件提供的工具类，用于统一生成标准格式字符串。

修正后的月度支出统计代码示例：

Future<int?> getMonthlyExpenseTotal(DateTime monthStart, DateTime monthEnd) async {
  final db = await database;
  // 将查询边界转为匹配存储格式的字符串
  final startStr = '${DateFormat('yyyy-MM-dd').format(monthStart)} 00:00:00';
  final endStr = '${DateFormat('yyyy-MM-dd').format(monthEnd)} 23:59:59';
  
  final result = await db.rawQuery(
    'SELECT SUM(AMOUNT) FROM $TABLE_EXPENSES WHERE $COLUMN_DATETIME >= ? AND $COLUMN_DATETIME <= ?',
    [startStr, endStr]
  );
  return Sqflite.firstIntValue(result);
}

已有存量数据的兼容处理 #

如果已经存储了大量MM-DD-YYYY格式的历史数据，不想做数据迁移，可以通过SQLite内置的strftime函数做格式转换后再筛选：

• 对应查询语句可写为：SELECT SUM(AMOUNT) FROM EXPENSES WHERE strftime('%Y-%m-%d', DATETIME) >= ? AND strftime('%Y-%m-%d', DATETIME) <= ?
• 注意这种逐行做格式转换的方式查询性能会低于直接存储标准格式，数据量较小时可以正常使用，数据量较大时建议还是迁移为标准存储格式。

读取时还原为DateTime对象 #

从数据库读取日期字符串后，不需要手动拆分拼接，直接调用Dart内置的DateTime.parse()方法，即可把ISO格式的字符串转为可用的DateTime对象：

final storedDateTimeString = row[COLUMN_DATETIME] as String;
final dateTime = DateTime.parse(storedDateTimeString);

注意：如果坚持使用MM-DD-YYYY作为存储格式，字典序比较会出现严重偏差，比如12-31-2023的字典序会比01-01-2024大，完全颠倒时间顺序，根本无法正确做范围筛选。

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