Knex.js中如何从MySQL datetime列按日期筛选数据

在Knex.js操作MySQL数据库的实际开发中，datetime类型的列会同时存储日期和时间信息，比如2024-05-20 14:30:25这样的格式。如果直接按日期字符串筛选，很容易因为时间部分的差异导致查询结果不符合预期，需要掌握正确的筛选方式。

基础筛选方法

使用DATE函数转换筛选

MySQL提供了DATE()函数，可以提取datetime列的日期部分，这样就能直接按日期匹配。在Knex.js中可以通过knex.raw调用该函数实现筛选。

const knex = require('knex')({
  client: 'mysql2',
  connection: {
    host: '127.0.0.1',
    user: 'root',
    password: '123456',
    database: 'test_db'
  }
});

// 筛选create_time为2024-05-20的所有记录
async function getRecordsByDate() {
  const result = await knex('user_log')
    .where(knex.raw('DATE(create_time) = ?', ['2024-05-20']))
    .select('*');
  return result;
}

范围查询筛选

如果不想使用函数转换，也可以通过指定日期的起始和结束时间范围来筛选，这种方式可以利用datetime列的索引，查询效率更高。

// 筛选create_time在2024-05-20当天的所有记录
async function getRecordsByRange() {
  const result = await knex('user_log')
    .whereBetween('create_time', ['2024-05-20 00:00:00', '2024-05-20 23:59:59'])
    .select('*');
  return result;
}

动态日期筛选场景

筛选最近N天的记录

实际开发中经常需要筛选最近几天的数据，可以结合MySQL的DATE_SUB函数实现动态日期计算。

// 筛选最近7天的记录
async function getRecentRecords() {
  const result = await knex('user_log')
    .where(knex.raw('create_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)'))
    .select('*');
  return result;
}

按用户输入的日期参数筛选

当需要根据前端传递的日期参数筛选时，要注意参数的合法性校验，避免SQL注入风险，Knex.js的参数绑定可以自动处理这个问题。

// 根据传入的日期字符串筛选，dateStr格式为YYYY-MM-DD
async function getRecordsByInputDate(dateStr) {
  // 简单校验日期格式
  if (!/^d{4}-d{2}-d{2}$/.test(dateStr)) {
    throw new Error('日期格式不正确');
  }
  const result = await knex('user_log')
    .where(knex.raw('DATE(create_time) = ?', [dateStr]))
    .select('*');
  return result;
}

时区相关问题处理

如果MySQL的时区和应用服务的时区不一致，筛选结果可能会出现偏差。可以在Knex.js连接配置中指定时区，或者在查询时统一转换时区。

// 连接时指定时区为东八区
const knex = require('knex')({
  client: 'mysql2',
  connection: {
    host: '127.0.0.1',
    user: 'root',
    password: '123456',
    database: 'test_db',
    timezone: '+08:00' // 设置为东八区时区
  }
});

// 查询时转换时区后筛选
async function getRecordsWithTimezone() {
  const result = await knex('user_log')
    .where(knex.raw('DATE(CONVERT_TZ(create_time, "+00:00", "+08:00")) = ?', ['2024-05-20']))
    .select('*');
  return result;
}

方法对比与选择建议

不同筛选方式的适用场景和性能有差异，以下是常见方法的对比：

在实际开发中，建议优先选择范围查询的方式，既能保证查询效率，也能避免时区带来的问题。如果数据量较小，使用DATE函数转换的方式会更简洁。同时要注意统一应用和数据库的时区配置，避免出现筛选结果偏差的问题。

Knex.jsMySQLdatetime_筛选日期查询修改时间：2026-07-08 18:57:33