From e34558412ae4b45e99d5efc25e0d9a47a97e1669 Mon Sep 17 00:00:00 2001 From: "C. McEnroe" Date: Wed, 8 Jul 2020 13:01:06 -0400 Subject: Convert timestamps to unix epoch time This saves 125 MB on my own database after VACUUM. --- litterbox.c | 10 +++++----- 1 file changed, 5 insertions(+), 5 deletions(-) (limited to 'litterbox.c') diff --git a/litterbox.c b/litterbox.c index a291ad0..70a5185 100644 --- a/litterbox.c +++ b/litterbox.c @@ -231,7 +231,7 @@ static void handleReplyMOTD(struct Message *msg) { static void handleReplyEndOfMOTD(struct Message *msg) { const char *sql = SQL( INSERT OR IGNORE INTO motds (time, network, motd) - VALUES (coalesce(datetime(:time), datetime('now')), :network, :motd); + VALUES (strftime('%s', coalesce(:time, 'now')), :network, :motd); ); sqlite3_stmt *stmt = dbPrepare(sql); dbBindText(stmt, ":time", msg->time); @@ -253,7 +253,7 @@ static void querySearch(struct Message *msg) { WITH results AS ( SELECT contexts.name AS context, - strftime('%Y-%m-%dT%H:%M:%SZ', events.time) AS time, + strftime('%Y-%m-%dT%H:%M:%SZ', events.time, 'unixepoch') AS time, events.type, names.nick, names.user, @@ -377,7 +377,7 @@ static void insertEvent( const char *sql = SQL( INSERT INTO events (time, type, context, name, target, message) SELECT - coalesce(datetime(:time), datetime('now')), + strftime('%s', coalesce(:time, 'now')), :type, context, names.name, :target, :message FROM contexts, names WHERE contexts.network = :network @@ -435,7 +435,7 @@ static void insertTopic( static sqlite3_stmt *stmt; const char *sql = SQL( INSERT OR IGNORE INTO topics (time, context, topic) - SELECT coalesce(datetime(:time), datetime('now')), context, :topic + SELECT strftime('%s', coalesce(:time, 'now')), context, :topic FROM contexts WHERE network = :network AND name = :context; ); dbPersist(&stmt, sql); @@ -546,7 +546,7 @@ static void insertEvents( const char *sql = SQL( INSERT INTO events (time, type, context, name, target, message) SELECT - coalesce(datetime(:time), datetime('now')), + strftime('%s', coalesce(:time, 'now')), :type, context, names.name, :target, :message FROM joins, contexts, names WHERE joins.nick = :nick -- cgit 1.4.1