Fwd: [PATCH] default values for localtime column, better-fitting types

[izzyb <izzyb@xxxxxxxxxxxxxxx>]

This makes sense to me.  Think storing timestamps in epoch format is the 
most portable solution.  So moving to a different database backend is 
easiest with epoch times.  Having a field for timezone might be useful 
but server timezone or utc can be assumed.  Either way, its easy to find 
dates within a range with it and is efficient format for the server to 
work with.

...Izzy

-------- Forwarded Message --------
Subject: 	Re: [PATCH] default values for localtime column, 
better-fitting types
Date: 	Sat, 3 Jun 2023 16:57:31 -0700
From: 	jrmu <jrmu@xxxxxxxxxx>
To: 	freeirc support <support@xxxxxxxxxxx>
CC: 	codeforce@xxxxxxxxxx



Was this patch tested? If so, IanJ, what do you think about using this
one?

--
jrmu
IRCNow (https://ircnow.org)

On Fri, Jun 02, 2023 at 02:12:05AM +0200, freeirc support wrote:

> adds a default value, in the same format as before (unix timestamp),
> so that complicated and failable migration scripts are not required.
>
> we use an INTEGER because sqlite does not have a special type for
> timestamps. "current_timestamp" and similar are stored as large
> and inefficent TEXT (a UTF-8 or 16 string).
>
> note that due to sqlite's weak typing, changing the column's declared
> type does not actually do much: it was already stored as an INTEGER
> to begin with, despite the declared VARCHAR type. changing this
> will just make the schema less misleading and confusing to those
> unfamiliar with sqlite's oddities.
>
> we cannot give a default value to the date column, as botnow
> calculates the value with respect to the current timezone. despite
> the name, localtime does not consider the local timezone; this is
> unchanged from previous behavior.
> ---
> table.sql | 12 ++++++------
> 1 file changed, 6 insertions(+), 6 deletions(-)
>
> diff --git a/table.sql b/table.sql
> index 1b71f51..f0e4568 100644
> --- a/table.sql
> +++ b/table.sql
> @@ -7,7 +7,7 @@ CREATE TABLE bnc (
> username VARCHAR(32),
> email VARCHAR(100),
> password VARCHAR(100),
> - localtime VARCHAR(100),
> + localtime INTEGER DEFAULT (unixepoch()),
> captcha INTEGER
> );
> CREATE TABLE shell (
> @@ -19,7 +19,7 @@ CREATE TABLE shell (
> username VARCHAR(32),
> email VARCHAR(100),
> password VARCHAR(100),
> - localtime VARCHAR(100),
> + localtime INTEGER DEFAULT (unixepoch()),
> captcha INTEGER
> );
> CREATE TABLE mail (
> @@ -31,7 +31,7 @@ CREATE TABLE mail (
> username VARCHAR(32),
> email VARCHAR(100),
> password VARCHAR(100),
> - localtime VARCHAR(100),
> + localtime INTEGER DEFAULT (unixepoch()),
> captcha INTEGER
> );
> CREATE TABLE www (
> @@ -73,13 +73,13 @@ CREATE TABLE irc (
> identified INTEGER,
> ctcpversion VARCHAR(100),
> ctcptime VARCHAR(100),
> - localtime VARCHAR(100),
> + localtime INTEGER DEFAULT (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 +89,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