[PATCH] add generated column for iso8601 date

[freeirc support <support@xxxxxxxxxxx>]

---
 table.sql | 16 ++++++++++------
 1 file changed, 10 insertions(+), 6 deletions(-)

diff --git a/table.sql b/table.sql
index 1b71f51..764865d 100644
--- a/table.sql
+++ b/table.sql
@@ -7,7 +7,8 @@ CREATE TABLE bnc (
   username VARCHAR(32),
   email VARCHAR(100),
   password VARCHAR(100),
-  localtime VARCHAR(100),
+  localtime INTEGER DEFAULT (unixepoch()),
+  datetime VARCHAR(25) AS (datetime(localtime, 'unixepoch')),
   captcha INTEGER
 );
 CREATE TABLE shell (
@@ -19,7 +20,8 @@ CREATE TABLE shell (
   username VARCHAR(32),
   email VARCHAR(100),
   password VARCHAR(100),
-  localtime VARCHAR(100),
+  localtime INTEGER DEFAULT (unixepoch()),
+  datetime VARCHAR(25) AS (datetime(localtime, 'unixepoch')),
   captcha INTEGER
 );
 CREATE TABLE mail (
@@ -31,7 +33,8 @@ CREATE TABLE mail (
   username VARCHAR(32),
   email VARCHAR(100),
   password VARCHAR(100),
-  localtime VARCHAR(100),
+  localtime INTEGER DEFAULT (unixepoch()),
+  datetime VARCHAR(25) AS (datetime(localtime, 'unixepoch')),
   captcha INTEGER
 );
 CREATE TABLE www (
@@ -73,13 +76,14 @@ CREATE TABLE irc (
   identified INTEGER,
   ctcpversion VARCHAR(100),
   ctcptime VARCHAR(100),
-  localtime VARCHAR(100),
+  localtime INTEGER DEFAULT (unixepoch()),
+  datetime VARCHAR(25) AS (datetime(localtime, 'unixepoch')),
   oper INTEGER,
   idle INTEGER,
   ssl INTEGER,
   epochtime INTEGER,
   chans VARCHAR(200),
-  date VARCHAR(100)
+  date INTEGER
 );
 CREATE TABLE smtp (
   id INTEGER PRIMARY KEY,
@@ -89,7 +93,7 @@ CREATE TABLE smtp (
   deliveredto VARCHAR(100),
   received VARCHAR(1000),
   dkim VARCHAR(1000),
-  date VARCHAR(100),
+  date INTEGER,
   other VARCHAR(5000),
   content VARCHAR(100),
   mime VARCHAR(100),
-- 
2.35.1