Good to know. Is that actually documented anywhere? All I see is...
https://www.sqlite.org/lang_datefunc.html "Format 11, the string 'now', is converted into the current date and time as obtained from the xCurrentTime method of the sqlite3_vfs object in use." https://www.sqlite.org/c3ref/vfs.html "The xCurrentTime() method returns a Julian Day Number for the current date and time as a floating point value. The xCurrentTimeInt64() method returns, as an integer, the Julian Day Number multiplied by 86400000 (the number of milliseconds in a 24-hour day). SQLite will use the xCurrentTimeInt64() method to get the current date and time if that method is available (if iVersion is 2 or greater and the function pointer is not NULL) and will fall back to xCurrentTime() if xCurrentTimeInt64() is unavailable." Nothing seems to say "this (is/should be) in (UTC/localtime)" or even "the default vfs object gives UTC, but this is an implementation detail and should not be relied upon" Is there any way to know what you're getting? Or to explicitly ask for one? -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf Sent: Wednesday, August 29, 2018 3:45 PM To: SQLite mailing list Subject: Re: [sqlite] Get data in one query ... 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
