Compare the efficiency when DATETIME (actually stored as TEXT in SQLite3) is replaced by INTEGER (Unix Epoch in milliseconds)

benchmark_sqlite_insert.js

var sqlite3 = require('sqlite3').verbose();
var db = new sqlite3.Database('benchmark_tmp.db');
var dateFormat = require('dateformat');

const ROWS = 100000

db.serialize(function() {
  let begin
  db.serialize(function() {
    db.run("CREATE TABLE date_time (ctime DATETIME)")
    db.run("CREATE TABLE unix_epoch (ctime INTEGER)")
    var date_time_statement = db.prepare("INSERT INTO date_time(ctime) VALUES (?)");
    var unix_epoch_statement = db.prepare("INSERT INTO unix_epoch(ctime) VALUES (?)");
    begin = Date.now()
    for (var i = 0; i < ROWS; i++) {
      let d = new Date(begin + (i * (Math.random() * 1000)))
      date_time_statement.run(dateFormat(d, 'yyyy-mm-dd HH:MM:ss'));
      unix_epoch_statement.run(d.getTime());
    }
    date_time_statement.finalize();
  }).serialize(function() {
    console.log(`Start to INSERT ${ROWS * 2} rows......`)
  })
});

benchmark_sqlite_select.js

var sqlite3 = require('sqlite3').verbose();
var db = new sqlite3.Database('benchmark_tmp.db');
var dateFormat = require('dateformat');
  let dt_from = Date.now()
  db.each("SELECT * FROM date_time WHERE ctime < ?", [dateFormat(new Date(Date.now() + 100000), 'yyyy-mm-dd HH:MM:ss')], function (err, row) {}, function(err, rowsCount) {
    console.log(`SELECT ${rowsCount} rows from Date_Time DATETIME! Cost ${Date.now()-dt_from} ms`)
  });
  let ue_from = Date.now()
  db.each("SELECT * FROM unix_epoch WHERE ctime < ?", [Date.now() + 100000], function (err, row) {}, function(err, rowsCount) {
    console.log(`SELECT ${rowsCount} rows from Unix_Epoch with INTEGER! Cost ${Date.now()-ue_from} ms`)
  });

run.sh

rm benchmark_tmp.db; time node benchmark_sqlite_insert.js; node benchmark_sqlite_select.js