Re: opendkim-2.5.0.Beta4 & sqlite3

On Sun, 27 Nov 2011, Andreas Schulze wrote:
> 1. Debian lenny, old sqlite3:
> # sqlite3 opendkim
> SQLite version 3.5.9
> Enter ".help" for instructions
> sqlite> SELECT COUNT(*) AS domains, MIN(l) AS 'min duration', MAX(l) AS 'max duration', AVG(l) AS 'mean duration', STDDEV_POP(l) AS 'duration stddev' FROM (SELECT COUNT(*) AS c, SUM(messages.spam)/COUNT(*) AS r, DATEDIFF(MAX(messages.msgtime), MIN(messages.msgtime)) AS l FROM signatures JOIN messages ON signatures.message = messages.id WHERE messages.id >= 5855122 AND NOT spam = -1 AND pass = 1 GROUP BY signatures.domain) t1 WHERE r >= 0.75 AND c >= 2;
> SQL error: no such function: DATEDIFF
> sqlite>

How does one compare two dates in SQLite 3 and get a difference in units
of days?

> 2. Debian squeeze, newer sqlite
> # SQLite version 3.7.3
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> SELECT COUNT(*) AS domains, MIN(l) AS 'min duration', MAX(l) AS 'max duration', AVG(l) AS 'mean duration', STDDEV_POP(l) AS 'duration stddev' FROM (SELECT COUNT(*) AS c, SUM(messages.spam)/COUNT(*) AS r, DATEDIFF(MAX(messages.msgtime), MIN(messages.msgtime)) AS l FROM signatures JOIN messages ON signatures.message = messages.id WHERE messages.id >= 5855122 AND NOT spam = -1 AND pass = 1 GROUP BY signatures.domain) t1 WHERE r >= 0.75 AND c >= 2;
> Error: no such function: STDDEV_POP
> sqlite>

MySQL's documentation says STDDEV_POP() is the SQL standard function for
computing a standard deviation based on a population. What does SQLite
use? STDDEV_SAMP()?

STDDEV_POP() is just the square root of VAR_POP(), if that helps.
Received on Sun Nov 27 2011 - 04:42:49 PST