... don't forget that Date('now') returns the UT1 date, not the local (as in Wall Clock/Calendar) date ... date('now', 'localtime') gives the local date in accordance with the timezone where your computer thinks it is located and should always be accurate for 'now' but maybe not a few years in the past on Operating Systems that do not contain/use a full set of UT1 -> localtime (timezone) conversion rules (such as Windows).
--- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Cecil Westerhof >Sent: Wednesday, 29 August, 2018 12:46 >To: SQLite mailing list >Subject: Re: [sqlite] Get data in one query > >2018-08-29 18:58 GMT+02:00 Cecil Westerhof <cldwester...@gmail.com>: > >> 2018-08-29 18:06 GMT+02:00 R Smith <ryansmit...@gmail.com>: >> >>> >>> SELECT SUM(Tot) AS Tot, SUM(Late) AS Late >>> FROM (SELECT 1 AS Tot, (time NOT LIKE '%:00') AS Late >>> FROM messages >>> WHERE date = DATE('now') >>> ) >>> >> >> Works like a charm. Thanks. >> >> I made it even more useful: >> SELECT Total >> , Late >> , CAST((Late * 100.0) / Total + .5 AS INTEGER) AS Percentage >> FROM ( >> SELECT SUM(total) AS Total >> , SUM(late) AS Late >> FROM ( >> SELECT 1 AS Total >> , (time NOT LIKE '%:00') AS Late >> FROM messages >> WHERE date = DATE('now') >> ) >> ) >> > >And even more useful: >SELECT date >, Total >, Late >, CAST((Late * 100.0) / Total + .5 AS INTEGER) AS Percentage >FROM ( > SELECT date > , SUM(total) AS Total > , SUM(late) AS Late > FROM ( > SELECT date > , 1 AS Total > , (time NOT LIKE '%:00') AS Late > FROM messages > WHERE date >= DATE('now', '-7 days') > AND date < DATE('now') > ) > GROUP BY date >) >ORDER BY date DESC > >-- >Cecil Westerhof >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users