Convert timestamps to unix epoch time

This saves 125 MB on my own database after VACUUM.

4 files changed, 27 insertions, 16 deletions

diff --git a/database.h b/database.h
index a0cba04..fb59140 100644
--- a/database.h
+++ b/database.h
@@ -42,7 +42,7 @@
 
 #define DATABASE_PATH "litterbox/litterbox.sqlite"
 
-enum { DatabaseVersion = 3 };
+enum { DatabaseVersion = 4 };
 
 #define ENUM_TYPE \
 	X(Privmsg, "privmsg") \
@@ -323,7 +323,7 @@ static const char *InitSQL = SQL(
 		UNIQUE (host, port)
 	);
 
-	PRAGMA user_version = 3;
+	PRAGMA user_version = 4;
 
 	COMMIT TRANSACTION;
 );
@@ -363,6 +363,13 @@ static const char *MigrationSQL[] = {
 		CREATE INDEX eventsTime ON events (time);
 		PRAGMA user_version = 3;
 	),
+
+	SQL(
+		UPDATE motds SET time = strftime('%s', time);
+		UPDATE topics SET time = strftime('%s', time);
+		UPDATE events SET time = strftime('%s', time);
+		PRAGMA user_version = 4;
+	),
 };
 
 static inline void dbMigrate(void) {
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
diff --git a/scoop.c b/scoop.c
index 078fc06..fed05ec 100644
--- a/scoop.c
+++ b/scoop.c
@@ -331,13 +331,12 @@ static char select[4096] = SQL(
 		CASE WHEN :local THEN
 			strftime(
 				coalesce(:format, '%Y-%m-%dT%H:%M:%S'),
-				events.time,
-				'localtime'
+				events.time, 'unixepoch', 'localtime'
 			)
 		ELSE
 			strftime(
 				coalesce(:format, '%Y-%m-%dT%H:%M:%SZ'),
-				events.time
+				events.time, 'unixepoch'
 			)
 		END AS time,
 		events.type,
@@ -402,8 +401,13 @@ int main(int argc, char *argv[]) {
 	for (int opt; 0 < (opt = getopt(argc, argv, Opts));) {
 		switch (opt) {
 			break; case 'D': {
-				append(where, SQL(AND events.time >= date(:date)));
-				append(where, SQL(AND events.time < date(:date, '+1 day')));
+				append(
+					where,
+					SQL(
+						AND events.time >= strftime('%s', :date)
+						AND events.time < strftime('%s', :date, '+1 day')
+					)
+				);
 				binds[n++] = Bind(":date", optarg, 0);
 			}
 			break; case 'F': {
@@ -424,11 +428,11 @@ int main(int argc, char *argv[]) {
 				binds[n++] = Bind(":target", optarg, 0);
 			}
 			break; case 'a': {
-				append(where, SQL(AND events.time >= datetime(:after)));
+				append(where, SQL(AND events.time >= strftime('%s', :after)));
 				binds[n++] = Bind(":after", optarg, 0);
 			}
 			break; case 'b': {
-				append(where, SQL(AND events.time < datetime(:before)));
+				append(where, SQL(AND events.time < strftime('%s', :before)));
 				binds[n++] = Bind(":before", optarg, 0);
 			}
 			break; case 'c': {
diff --git a/unscoop.c b/unscoop.c
index 4392816..033c1dd 100644
--- a/unscoop.c
+++ b/unscoop.c
@@ -263,8 +263,8 @@ static void prepareInsert(void) {
 		SELECT
 			// SQLite expects a colon in the timezine, but ISO8601 does not.
 			CASE WHEN :time LIKE '%Z'
-				THEN datetime(:time)
-				ELSE datetime(substr(:time, 1, 22) || ':' || substr(:time, -2))
+				THEN strftime('%s', :time)
+				ELSE strftime('%s', substr(:time, 1, 22) || ':' || substr(:time, -2))
 			END,
 			:type, context, names.name, :target, :message
 		FROM contexts, names