Julian Day is defined in terms of Universal Time. https://en.wikipedia.org/wiki/Julian_day
2018-08-29 22:33 GMT+02:00, David Raymond <david.raym...@tomtom.com>: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
