another oops I see Ryan pretty much posted the same as me 5 minutes earlier - I'll go back to bed :)
Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 12 April 2017 at 14:08, Ron Barnes <rbar...@njdevils.net> wrote: > Hello Ryan, > > That Code below worked as you said it should. Awesome! And Thank you! > > I now have the days difference for each row. > > I have one other question if I may pose it to you, how do I count the > number of rows, less than a day, or a week or a year and so forth? > > I tried this code and a few variants of it but I keep getting errors when > trying to execute. > > Would you examine my code for errors? > > SELECT category, COUNT(*) AS Expr1 > FROM > > ((SELECT replace(substr(VI_Creation_Date, 1, 10), '/','-')||' 12:00:00' > AS ISO_Date, julianday('Now') - julianday(replace(substr(VI_Creation_Date,1, > 10), '/','-')||' 12:00:00') AS DaysSince) > > WHEN DaysSince < 2 THEN 'Under 1 Day' > WHEN DaysSince < 8 THEN 'Under 1 Week' > WHEN DaysSince < 32 THEN 'Under 1 Month' > WHEN DaysSince < 366 THEN 'Under 1 Year' > WHEN DaysSince < 366 THEN 'Under 1 Year' > WHEN DaysSince < 731 THEN 'Under 2 Year' > WHEN DaysSince < 1826 THEN 'Under 5 Years' > WHEN DaysSince < 3651 THEN 'Under 10 Years' > ELSE 'Over 10 Years' END) AS category > FROM Volume_Information) derivedtbl_1 > GROUP BY category > > Thanks, > -Ron > > > > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of R Smith > Sent: Wednesday, April 12, 2017 8:32 AM > To: sqlite-users@mailinglists.sqlite.org > Subject: Re: [sqlite] SQLite - Interrogate Date/Time field Statement > question > > > On 2017/04/12 2:13 PM, Ron Barnes wrote: > > Hi Jim, > > > > I ran an overnight job and converted 300+ million dates to the ISO 8601 > format. > > > > Here are examples of the new dates. > > > > 2017/04/10 07:24:15 PM > > 2017/03/07 08:08:58 AM > > 2016/11/06 12:35:15 PM > > > > Since this should be easier how would you go about determining the > Day(s) Difference from the current date? > > This is much friendlier. Do you care about the time? If not the conversion > is VERY easy: > > SELECT replace(substr(VI_Creation_Date, 1, 10), '/','-')||' 12:00:00' > AS ISO_Date, > julianday('Now') - julianday(replace(substr(VI_Creation_Date, > 1, 10), '/','-')||' 12:00:00') AS DaysSince > FROM Volume_Information > > Cheers! > Ryan > _______________________________________________ > 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
