... 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-
>[email protected]] 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 <[email protected]>:
>
>> 2018-08-29 18:06 GMT+02:00 R Smith <[email protected]>:
>>
>>>
>>> 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
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
