SQLite - Interrogate Date/Time field Statement question

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
19 messages Options
Reply | Threaded
Open this post in threaded view
|

SQLite - Interrogate Date/Time field Statement question

Ron Barnes
Hello all,

To everyone who helped me before - thank you very much!

I'm coding in Visual Basic .NET (Visual Studio 2015) Community.

I have to count a Date/Time field and the problem is, this field contains data in a format I'm not sure can be counted.

I need to count all the dates in the field but the dates are a combined Date and time in the format examples below.
My goal is to use the current Date/time ('NOW') and calculate the time difference in days, from my DB Sourced field.

I need to capture...
Less than 1 month old
1 month old
2 months old
1 year old.....
all the way to greater than 10 years old.

Is this even possible in SQLite and if so, how would I go about doing it?

I have been googling a few queries and come up blank.

I try this code and differing combinations of it but it always returns NULL.

SELECT CAST
((datetime(julianday(datetime('now'))) - JulianDay(VI_Creation_Date)) As Integer)
FROM Volume_Information

Here is what I have to work with.

Table Name:
Volume_Information

Column name:
VI_Creation_Date

Date Format:
MM/DD/CCYY HH:MM:SS AM/PM

Examples:

10/30/2015 2:28:30 AM
2/13/2016 7:51:04 AM
5/15/2016 12:06:24 PM
10/7/2016 1:27:13 PM

Any Help would be greatly appreciated,

Thanks,

-Ron

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite - Interrogate Date/Time field Statement question

Jim Callahan
Can you convert the dates to ISO 8601 date time format?
https://en.wikipedia.org/wiki/ISO_8601

YYYY-MM-DD hh:mm:ss

ISO date strings (when zero filled) are sortable which necessarily includes
comparable (Java speak).
By "zero filled" I mean for March you have "03" and not just "3".

Then if you could generate/populate the boundary values in ISO format; the
comparisons would be straightforward and you could avoid the julian date
conversion.

Another disadvantage of Julian dates are the different base years used by
applications including Unix, MS Access, MS Excel for Windows and MS Excel
for MacIntosh. Each application is internally consistent, but the minute
you exchange data between applications...
https://support.microsoft.com/en-us/help/214330/differences-
between-the-1900-and-the-1904-date-system-in-excel

Your specification actually requires day counts; so you may need Julian
dates after all.

Jim Callahan
Orlando, FL



On Tue, Apr 11, 2017 at 7:24 PM, Ron Barnes <[hidden email]> wrote:

> Hello all,
>
> To everyone who helped me before - thank you very much!
>
> I'm coding in Visual Basic .NET (Visual Studio 2015) Community.
>
> I have to count a Date/Time field and the problem is, this field contains
> data in a format I'm not sure can be counted.
>
> I need to count all the dates in the field but the dates are a combined
> Date and time in the format examples below.
> My goal is to use the current Date/time ('NOW') and calculate the time
> difference in days, from my DB Sourced field.
>
> I need to capture...
> Less than 1 month old
> 1 month old
> 2 months old
> 1 year old.....
> all the way to greater than 10 years old.
>
> Is this even possible in SQLite and if so, how would I go about doing it?
>
> I have been googling a few queries and come up blank.
>
> I try this code and differing combinations of it but it always returns
> NULL.
>
> SELECT CAST
> ((datetime(julianday(datetime('now'))) - JulianDay(VI_Creation_Date)) As
> Integer)
> FROM Volume_Information
>
> Here is what I have to work with.
>
> Table Name:
> Volume_Information
>
> Column name:
> VI_Creation_Date
>
> Date Format:
> MM/DD/CCYY HH:MM:SS AM/PM
>
> Examples:
>
> 10/30/2015 2:28:30 AM
> 2/13/2016 7:51:04 AM
> 5/15/2016 12:06:24 PM
> 10/7/2016 1:27:13 PM
>
> Any Help would be greatly appreciated,
>
> Thanks,
>
> -Ron
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite - Interrogate Date/Time field Statement question

Ron Barnes
Hi Jim,

I could alter the program that populates the Date/Time Column to the format you specify.  I'm trying real hard not to as that program has been in use for many years and it would be a significant undertaking to convert the program then convert the existing data.  Not saying I won't do it as I'm at that point, just wondering if it's possible to avoid that route.

If I converted the date/time field, would it be easier to create counts?

If you could, would you be able to offer a sample Select statement I can alter to fit my needs?  

Thank you very much for the reply!

Side note, I'll be visiting Disney in July!

Regards,

-Ron

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Jim Callahan
Sent: Tuesday, April 11, 2017 9:15 PM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

Can you convert the dates to ISO 8601 date time format?
https://en.wikipedia.org/wiki/ISO_8601

YYYY-MM-DD hh:mm:ss

ISO date strings (when zero filled) are sortable which necessarily includes comparable (Java speak).
By "zero filled" I mean for March you have "03" and not just "3".

Then if you could generate/populate the boundary values in ISO format; the comparisons would be straightforward and you could avoid the julian date conversion.

Another disadvantage of Julian dates are the different base years used by applications including Unix, MS Access, MS Excel for Windows and MS Excel for MacIntosh. Each application is internally consistent, but the minute you exchange data between applications...
https://support.microsoft.com/en-us/help/214330/differences-
between-the-1900-and-the-1904-date-system-in-excel

Your specification actually requires day counts; so you may need Julian dates after all.

Jim Callahan
Orlando, FL



On Tue, Apr 11, 2017 at 7:24 PM, Ron Barnes <[hidden email]> wrote:

> Hello all,
>
> To everyone who helped me before - thank you very much!
>
> I'm coding in Visual Basic .NET (Visual Studio 2015) Community.
>
> I have to count a Date/Time field and the problem is, this field
> contains data in a format I'm not sure can be counted.
>
> I need to count all the dates in the field but the dates are a
> combined Date and time in the format examples below.
> My goal is to use the current Date/time ('NOW') and calculate the time
> difference in days, from my DB Sourced field.
>
> I need to capture...
> Less than 1 month old
> 1 month old
> 2 months old
> 1 year old.....
> all the way to greater than 10 years old.
>
> Is this even possible in SQLite and if so, how would I go about doing it?
>
> I have been googling a few queries and come up blank.
>
> I try this code and differing combinations of it but it always returns
> NULL.
>
> SELECT CAST
> ((datetime(julianday(datetime('now'))) - JulianDay(VI_Creation_Date))
> As
> Integer)
> FROM Volume_Information
>
> Here is what I have to work with.
>
> Table Name:
> Volume_Information
>
> Column name:
> VI_Creation_Date
>
> Date Format:
> MM/DD/CCYY HH:MM:SS AM/PM
>
> Examples:
>
> 10/30/2015 2:28:30 AM
> 2/13/2016 7:51:04 AM
> 5/15/2016 12:06:24 PM
> 10/7/2016 1:27:13 PM
>
> Any Help would be greatly appreciated,
>
> Thanks,
>
> -Ron
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite - Interrogate Date/Time field Statement question

Jim Callahan
This code:

SELECT ( substr('02/13/2016',7,4) || '-'
      || substr('02/13/2016',1,2) || '-'
      || substr('02/13/2016',4,2) ) ;

yields

2016-02-13

The above code, is dependent on fixed length strings (the leading zero) in
other words '02/13/2016' and not '2/13/2016'.

If you do not have fixed length date strings,  you would probably have to
use
globs or regular expressions.

*glob(X,Y)*

The glob(X,Y) function is equivalent to the expression "*Y GLOB X*". Note
that the X and Y arguments are reversed in the glob() function relative to
the infix GLOB <https://sqlite.org/lang_expr.html#glob> operator.

https://sqlite.org/lang_corefunc.html#glob


The REGEXP operator is a special syntax for the regexp() user function. No
regexp() user function is defined by default and so use of the REGEXP
operator will normally result in an error message. If an application-defined
SQL function <https://sqlite.org/c3ref/create_function.html> named "regexp"
is added at run-time, then the "*X* REGEXP *Y*" operator will be
implemented as a call to "regexp(*Y*,*X*)".

https://sqlite.org/lang_expr.html


Type of regular expression needed:
https://social.msdn.microsoft.com/Forums/en-US/7f38ee7b-15e2-4e2c-8389-1266f496e4b2/regular-expression-to-get-date-format-from-string?forum=csharplanguage

​Jim Callahan

On Tue, Apr 11, 2017 at 10:00 PM, Ron Barnes <[hidden email]> wrote:

> Hi Jim,
>
> I could alter the program that populates the Date/Time Column to the
> format you specify.  I'm trying real hard not to as that program has been
> in use for many years and it would be a significant undertaking to convert
> the program then convert the existing data.  Not saying I won't do it as
> I'm at that point, just wondering if it's possible to avoid that route.
>
> If I converted the date/time field, would it be easier to create counts?
>
> If you could, would you be able to offer a sample Select statement I can
> alter to fit my needs?
>
> Thank you very much for the reply!
>
> Side note, I'll be visiting Disney in July!
>
> Regards,
>
> -Ron
>
> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of Jim Callahan
> Sent: Tuesday, April 11, 2017 9:15 PM
> To: SQLite mailing list <[hidden email]>
> Subject: Re: [sqlite] SQLite - Interrogate Date/Time field Statement
> question
>
> Can you convert the dates to ISO 8601 date time format?
> https://en.wikipedia.org/wiki/ISO_8601
>
> YYYY-MM-DD hh:mm:ss
>
> ISO date strings (when zero filled) are sortable which necessarily
> includes comparable (Java speak).
> By "zero filled" I mean for March you have "03" and not just "3".
>
> Then if you could generate/populate the boundary values in ISO format; the
> comparisons would be straightforward and you could avoid the julian date
> conversion.
>
> Another disadvantage of Julian dates are the different base years used by
> applications including Unix, MS Access, MS Excel for Windows and MS Excel
> for MacIntosh. Each application is internally consistent, but the minute
> you exchange data between applications...
> https://support.microsoft.com/en-us/help/214330/differences-
> between-the-1900-and-the-1904-date-system-in-excel
>
> Your specification actually requires day counts; so you may need Julian
> dates after all.
>
> Jim Callahan
> Orlando, FL
>
>
>
> On Tue, Apr 11, 2017 at 7:24 PM, Ron Barnes <[hidden email]> wrote:
>
> > Hello all,
> >
> > To everyone who helped me before - thank you very much!
> >
> > I'm coding in Visual Basic .NET (Visual Studio 2015) Community.
> >
> > I have to count a Date/Time field and the problem is, this field
> > contains data in a format I'm not sure can be counted.
> >
> > I need to count all the dates in the field but the dates are a
> > combined Date and time in the format examples below.
> > My goal is to use the current Date/time ('NOW') and calculate the time
> > difference in days, from my DB Sourced field.
> >
> > I need to capture...
> > Less than 1 month old
> > 1 month old
> > 2 months old
> > 1 year old.....
> > all the way to greater than 10 years old.
> >
> > Is this even possible in SQLite and if so, how would I go about doing it?
> >
> > I have been googling a few queries and come up blank.
> >
> > I try this code and differing combinations of it but it always returns
> > NULL.
> >
> > SELECT CAST
> > ((datetime(julianday(datetime('now'))) - JulianDay(VI_Creation_Date))
> > As
> > Integer)
> > FROM Volume_Information
> >
> > Here is what I have to work with.
> >
> > Table Name:
> > Volume_Information
> >
> > Column name:
> > VI_Creation_Date
> >
> > Date Format:
> > MM/DD/CCYY HH:MM:SS AM/PM
> >
> > Examples:
> >
> > 10/30/2015 2:28:30 AM
> > 2/13/2016 7:51:04 AM
> > 5/15/2016 12:06:24 PM
> > 10/7/2016 1:27:13 PM
> >
> > Any Help would be greatly appreciated,
> >
> > Thanks,
> >
> > -Ron
> >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite - Interrogate Date/Time field Statement question

R Smith
In reply to this post by Ron Barnes


On 2017/04/12 1:24 AM, Ron Barnes wrote:

> Hello all,
>
> To everyone who helped me before - thank you very much!
>
> I'm coding in Visual Basic .NET (Visual Studio 2015) Community.
>
> I have to count a Date/Time field and the problem is, this field contains data in a format I'm not sure can be counted.
>
> I need to count all the dates in the field but the dates are a combined Date and time in the format examples below.
> My goal is to use the current Date/time ('NOW') and calculate the time difference in days, from my DB Sourced field.
>
> I need to capture...
> Less than 1 month old
> 1 month old
> 2 months old
> 1 year old.....
> all the way to greater than 10 years old.

Your data is in a bad format as others pointed out, and probably the
fastest solution would be to fix it in a program, however, SQLite can
fix it.

This next script will use CTE's to interpret the date, then reassemble
it as ISO8601 date format and then calculate the elapsed days since that
date.
I've used your example dates in the test, plus added a few of mine to
make sure we catch every possibility.

To understand better what is happening, you can query any of the CTE
tables (DA, DB, DC, etc.) in the main query.

Also, I do the re-interpretation to use Julianday, but with a bit of
cleverness, once you've interpreted the date constituents (CTE table DC
below) you can already calculate the elapsed days, months or years.

Have fun!

       -- Processing SQL in: E:\Documents\SQLiteAutoScript.sql
       -- SQLite version 3.17.0  [ Release: 2017-02-13 ]  on SQLitespeed
    version 2.0.2.4.

       -- Script Items: 4          Parameter Count: 0
       -- 2017-04-12 13:43:15.875  |  [Info]       Script Initialized,
    Started executing...
       --
    ================================================================================================

    CREATE TEMPORARY TABLE Volume_Information(
       ID INTEGER PRIMARY KEY,
       VI_Creation_Date TEXT
    );


    INSERT INTO Volume_Information(VI_Creation_Date) VALUES
    ('10/30/2015 2:28:30 AM'),
    ('2/13/2016 7:51:04 AM'),
    ('5/15/2016 12:06:24 PM'),
    ('10/7/2016 1:27:13 PM'),
    ('3/3/2017 1:7:3 PM'),
    ('10/1/2016 6:59:18 AM'),
    ('10/09/2016 11:27:13 PM');


    WITH DA(ID,DT) AS (
          SELECT ID, replace(replace(replace(replace(VI_Creation_Date,'
    ',':'),'/',':'),'AM','0'),'PM','12')||':'
            FROM Volume_Information
    ), DB(i, k, l, c, r) AS (
           SELECT DA.ID, 0, 1, DA.DT, -1
             FROM DA
         UNION ALL
           SELECT i, k+1, instr(c, ':'), substr(c, instr( c, ':' ) + 1),
                  CAST(trim(substr(c, 1, instr(c, ':') - 1)) AS INT)
             FROM DB
            WHERE l > 0
    ), DC(ID, YY, MM, DD, HH, NN, SS, AP) AS (
           SELECT DA.ID,
                  MAX(CASE WHEN k=3 THEN DB.r ELSE 0 END),
                  MAX(CASE WHEN k=1 THEN DB.r ELSE 0 END),
                  MAX(CASE WHEN k=2 THEN DB.r ELSE 0 END),
                  MAX(CASE WHEN k=4 THEN DB.r ELSE 0 END),
                  MAX(CASE WHEN k=5 THEN DB.r ELSE 0 END),
                  MAX(CASE WHEN k=6 THEN DB.r ELSE 0 END),
                  MAX(CASE WHEN k=7 THEN DB.r ELSE 0 END)
             FROM DA, DB
            WHERE DA.ID = DB.i AND DB.r >= 0 AND DB.k > 0
            GROUP BY DA.ID
    ), DD(ID, ISO_DT) AS (
           SELECT ID, YY||'-'||
              CASE WHEN MM > 9 THEN MM ELSE '0'||MM END||'-'||
              CASE WHEN DD > 9 THEN DD ELSE '0'||DD END||' '||
              CASE
                WHEN HH = 12 AND AP = 0 THEN '00'
                WHEN HH = 12 AND AP > 0 THEN AP
                WHEN HH + AP > 9 THEN HH + AP
                ELSE '0'||HH
              END||':'||
              CASE WHEN NN > 9 THEN NN ELSE '0'||NN END||':'||
              CASE WHEN SS > 9 THEN SS ELSE '0'||SS END
         FROM DC
    )
    SELECT VI.ID, VI.VI_Creation_Date, DD.ISO_DT,
    printf('%0.1f',julianday('now')-julianday(DD.ISO_DT)) AS DaysSince
       FROM Volume_Information AS VI
       JOIN DD ON DD.ID = VI.ID
      ORDER BY VI.ID
    ;

       --     VI.ID    | VI.VI_Creation_Date      |
    DD.ISO_DT             | DaysSince
       -- ------------ | ------------------------ |
    --------------------- | ---------
       --       1      | 10/30/2015 2:28:30 AM    | 2015-10-30
    02:28:30   |     530.4
       --       2      | 2/13/2016 7:51:04 AM     | 2016-02-13
    07:51:04   |     424.2
       --       3      | 5/15/2016 12:06:24 PM    | 2016-05-15
    12:06:24   |     332.0
       --       4      | 10/7/2016 1:27:13 PM     | 2016-10-07
    13:27:13   |     186.9
       --       5      | 3/3/2017 1:7:3 PM        | 2017-03-03
    13:07:03   |      39.9
       --       6      | 10/1/2016 6:59:18 AM     | 2016-10-01
    06:59:18   |     193.2
       --       7      | 10/09/2016 11:27:13 PM   | 2016-10-09
    23:27:13   |     184.5


    DROP TABLE Volume_Information;

       --   Script Stats: Total Script Execution Time:     0d 00h 00m
    and 00.022s
       --                 Total Script Query Time:         -- --- ---
    --- --.----
       --                 Total Database Rows Changed:     7
       --                 Total Virtual-Machine Steps:     6304
       --                 Last executed Item Index:        4
       --                 Last Script Error:
       --
    ------------------------------------------------------------------------------------------------

       -- 2017-04-12 13:43:15.881  |  [Success]    Script Success.
       -- 2017-04-12 13:43:15.881  |  [Success]    Transaction Rolled back.
       -- -------  DB-Engine Logs (Contains logged information from all
    DB connections during run)  ------
       -- [2017-04-12 13:43:15.843] APPLICATION : Script
    E:\Documents\SQLiteAutoScript.sql started at 13:43:15.843 on 12 April.
       -- [2017-04-12 13:43:15.881] ERROR (284) : automatic index on DB(i)
       --
    ================================================================================================






_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite - Interrogate Date/Time field Statement question

R Smith
Mailer messed up the format on a very important space... retry:

   -- Script Items: 4          Parameter Count: 0
   -- 2017-04-12 13:43:15.875  |  [Info]       Script Initialized,
Started executing...
   --
================================================================================================

CREATE TEMPORARY TABLE Volume_Information(
   ID INTEGER PRIMARY KEY,
   VI_Creation_Date TEXT
);


INSERT INTO Volume_Information(VI_Creation_Date) VALUES
('10/30/2015 2:28:30 AM'),
('2/13/2016 7:51:04 AM'),
('5/15/2016 12:06:24 PM'),
('10/7/2016 1:27:13 PM'),
('3/3/2017 1:7:3 PM'),
('10/1/2016 6:59:18 AM'),
('10/09/2016 11:27:13 PM');


WITH DA(ID,DT) AS (
      SELECT ID, replace(replace(replace(replace(
               VI_Creation_Date,' ', ':'), '/', ':'
             ), 'AM', '0'), 'PM', '12')||':'
        FROM Volume_Information
), DB(i, k, l, c, r) AS (
       SELECT DA.ID, 0, 1, DA.DT, -1
         FROM DA
     UNION ALL
       SELECT i, k+1, instr(c, ':'), substr(c, instr( c, ':' ) + 1),
              CAST(trim(substr(c, 1, instr(c, ':') - 1)) AS INT)
         FROM DB
        WHERE l > 0
), DC(ID, YY, MM, DD, HH, NN, SS, AP) AS (
       SELECT DA.ID,
              MAX(CASE WHEN k=3 THEN DB.r ELSE 0 END),
              MAX(CASE WHEN k=1 THEN DB.r ELSE 0 END),
              MAX(CASE WHEN k=2 THEN DB.r ELSE 0 END),
              MAX(CASE WHEN k=4 THEN DB.r ELSE 0 END),
              MAX(CASE WHEN k=5 THEN DB.r ELSE 0 END),
              MAX(CASE WHEN k=6 THEN DB.r ELSE 0 END),
              MAX(CASE WHEN k=7 THEN DB.r ELSE 0 END)
         FROM DA, DB
        WHERE DA.ID = DB.i AND DB.r >= 0 AND DB.k > 0
        GROUP BY DA.ID
), DD(ID, ISO_DT) AS (
       SELECT ID, YY||'-'||
          CASE WHEN MM > 9 THEN MM ELSE '0'||MM END||'-'||
          CASE WHEN DD > 9 THEN DD ELSE '0'||DD END||' '||
          CASE
            WHEN HH = 12 AND AP = 0 THEN '00'
            WHEN HH = 12 AND AP > 0 THEN AP
            WHEN HH + AP > 9 THEN HH + AP
            ELSE '0'||HH
          END||':'||
          CASE WHEN NN > 9 THEN NN ELSE '0'||NN END||':'||
          CASE WHEN SS > 9 THEN SS ELSE '0'||SS END
     FROM DC
)
SELECT VI.ID, VI.VI_Creation_Date, DD.ISO_DT,
printf('%0.1f',julianday('now')-julianday(DD.ISO_DT)) AS DaysSince
   FROM Volume_Information AS VI
   JOIN DD ON DD.ID = VI.ID
  ORDER BY VI.ID
;

-- VI.ID | VI.VI_Creation_Date     | DD.ISO_DT           | DaysSince
-- ----- | ----------------------- | ------------------- | ---------
--   1   | 10/30/2015 2:28:30 AM   | 2015-10-30 02:28:30 |     530.4
--   2   | 2/13/2016 7:51:04 AM    | 2016-02-13 07:51:04 |     424.2
--   3   | 5/15/2016 12:06:24 PM   | 2016-05-15 12:06:24 |     332.0
--   4   | 10/7/2016 1:27:13 PM    | 2016-10-07 13:27:13 |     186.9
--   5   | 3/3/2017 1:7:3 PM       | 2017-03-03 13:07:03 |      39.9
--   6   | 10/1/2016 6:59:18 AM    | 2016-10-01 06:59:18 |     193.2
--   7   | 10/09/2016 11:27:13 PM  | 2016-10-09 23:27:13 |     184.5


DROP TABLE Volume_Information;

   --   Script Stats: Total Script Execution Time:     0d 00h 00m and
00.022s
   --                 Total Script Query Time:         -- --- --- ---
--.----
   --                 Total Database Rows Changed:     7
   --                 Total Virtual-Machine Steps:     6304
   --                 Last executed Item Index:        4
   --                 Last Script Error:
   --
------------------------------------------------------------------------------------------------


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite - Interrogate Date/Time field Statement question

Ron Barnes
In reply to this post by Jim Callahan
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?

Thanks in advance,

-Ron

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Jim Callahan
Sent: Tuesday, April 11, 2017 11:22 PM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

This code:

SELECT ( substr('02/13/2016',7,4) || '-'
      || substr('02/13/2016',1,2) || '-'
      || substr('02/13/2016',4,2) ) ;

yields

2016-02-13

The above code, is dependent on fixed length strings (the leading zero) in other words '02/13/2016' and not '2/13/2016'.

If you do not have fixed length date strings,  you would probably have to use globs or regular expressions.

*glob(X,Y)*

The glob(X,Y) function is equivalent to the expression "*Y GLOB X*". Note that the X and Y arguments are reversed in the glob() function relative to the infix GLOB <https://sqlite.org/lang_expr.html#glob> operator.

https://sqlite.org/lang_corefunc.html#glob


The REGEXP operator is a special syntax for the regexp() user function. No
regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message. If an application-defined SQL function <https://sqlite.org/c3ref/create_function.html> named "regexp"
is added at run-time, then the "*X* REGEXP *Y*" operator will be implemented as a call to "regexp(*Y*,*X*)".

https://sqlite.org/lang_expr.html


Type of regular expression needed:
https://social.msdn.microsoft.com/Forums/en-US/7f38ee7b-15e2-4e2c-8389-1266f496e4b2/regular-expression-to-get-date-format-from-string?forum=csharplanguage

​Jim Callahan

On Tue, Apr 11, 2017 at 10:00 PM, Ron Barnes <[hidden email]> wrote:

> Hi Jim,
>
> I could alter the program that populates the Date/Time Column to the
> format you specify.  I'm trying real hard not to as that program has
> been in use for many years and it would be a significant undertaking
> to convert the program then convert the existing data.  Not saying I
> won't do it as I'm at that point, just wondering if it's possible to avoid that route.
>
> If I converted the date/time field, would it be easier to create counts?
>
> If you could, would you be able to offer a sample Select statement I
> can alter to fit my needs?
>
> Thank you very much for the reply!
>
> Side note, I'll be visiting Disney in July!
>
> Regards,
>
> -Ron
>
> -----Original Message-----
> From: sqlite-users
> [mailto:[hidden email]]
> On Behalf Of Jim Callahan
> Sent: Tuesday, April 11, 2017 9:15 PM
> To: SQLite mailing list <[hidden email]>
> Subject: Re: [sqlite] SQLite - Interrogate Date/Time field Statement
> question
>
> Can you convert the dates to ISO 8601 date time format?
> https://en.wikipedia.org/wiki/ISO_8601
>
> YYYY-MM-DD hh:mm:ss
>
> ISO date strings (when zero filled) are sortable which necessarily
> includes comparable (Java speak).
> By "zero filled" I mean for March you have "03" and not just "3".
>
> Then if you could generate/populate the boundary values in ISO format;
> the comparisons would be straightforward and you could avoid the
> julian date conversion.
>
> Another disadvantage of Julian dates are the different base years used
> by applications including Unix, MS Access, MS Excel for Windows and MS
> Excel for MacIntosh. Each application is internally consistent, but
> the minute you exchange data between applications...
> https://support.microsoft.com/en-us/help/214330/differences-
> between-the-1900-and-the-1904-date-system-in-excel
>
> Your specification actually requires day counts; so you may need
> Julian dates after all.
>
> Jim Callahan
> Orlando, FL
>
>
>
> On Tue, Apr 11, 2017 at 7:24 PM, Ron Barnes <[hidden email]> wrote:
>
> > Hello all,
> >
> > To everyone who helped me before - thank you very much!
> >
> > I'm coding in Visual Basic .NET (Visual Studio 2015) Community.
> >
> > I have to count a Date/Time field and the problem is, this field
> > contains data in a format I'm not sure can be counted.
> >
> > I need to count all the dates in the field but the dates are a
> > combined Date and time in the format examples below.
> > My goal is to use the current Date/time ('NOW') and calculate the
> > time difference in days, from my DB Sourced field.
> >
> > I need to capture...
> > Less than 1 month old
> > 1 month old
> > 2 months old
> > 1 year old.....
> > all the way to greater than 10 years old.
> >
> > Is this even possible in SQLite and if so, how would I go about doing it?
> >
> > I have been googling a few queries and come up blank.
> >
> > I try this code and differing combinations of it but it always
> > returns NULL.
> >
> > SELECT CAST
> > ((datetime(julianday(datetime('now'))) -
> > JulianDay(VI_Creation_Date)) As
> > Integer)
> > FROM Volume_Information
> >
> > Here is what I have to work with.
> >
> > Table Name:
> > Volume_Information
> >
> > Column name:
> > VI_Creation_Date
> >
> > Date Format:
> > MM/DD/CCYY HH:MM:SS AM/PM
> >
> > Examples:
> >
> > 10/30/2015 2:28:30 AM
> > 2/13/2016 7:51:04 AM
> > 5/15/2016 12:06:24 PM
> > 10/7/2016 1:27:13 PM
> >
> > Any Help would be greatly appreciated,
> >
> > Thanks,
> >
> > -Ron
> >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite - Interrogate Date/Time field Statement question

Ron Barnes
In reply to this post by R Smith
R Smith.... WOW!

Lol..... I just ran an overnight job to convert the dates to a more machine friendly format.

Looking at your code below, it is much more advanced than my skills can interpret.  I will attempt to extract the code below (minus your conversion logic) to grab the days difference and generate my counts.

I very much appreciate all your effort!!!  And to the SQLite community as well!


Regards,

-Ron

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of R Smith
Sent: Wednesday, April 12, 2017 7:49 AM
To: [hidden email]
Subject: Re: [sqlite] SQLite - Interrogate Date/Time field Statement question



On 2017/04/12 1:24 AM, Ron Barnes wrote:

> Hello all,
>
> To everyone who helped me before - thank you very much!
>
> I'm coding in Visual Basic .NET (Visual Studio 2015) Community.
>
> I have to count a Date/Time field and the problem is, this field contains data in a format I'm not sure can be counted.
>
> I need to count all the dates in the field but the dates are a combined Date and time in the format examples below.
> My goal is to use the current Date/time ('NOW') and calculate the time difference in days, from my DB Sourced field.
>
> I need to capture...
> Less than 1 month old
> 1 month old
> 2 months old
> 1 year old.....
> all the way to greater than 10 years old.

Your data is in a bad format as others pointed out, and probably the fastest solution would be to fix it in a program, however, SQLite can fix it.

This next script will use CTE's to interpret the date, then reassemble it as ISO8601 date format and then calculate the elapsed days since that date.
I've used your example dates in the test, plus added a few of mine to make sure we catch every possibility.

To understand better what is happening, you can query any of the CTE tables (DA, DB, DC, etc.) in the main query.

Also, I do the re-interpretation to use Julianday, but with a bit of cleverness, once you've interpreted the date constituents (CTE table DC
below) you can already calculate the elapsed days, months or years.

Have fun!

       -- Processing SQL in: E:\Documents\SQLiteAutoScript.sql
       -- SQLite version 3.17.0  [ Release: 2017-02-13 ]  on SQLitespeed
    version 2.0.2.4.

       -- Script Items: 4          Parameter Count: 0
       -- 2017-04-12 13:43:15.875  |  [Info]       Script Initialized,
    Started executing...
       --
    ================================================================================================

    CREATE TEMPORARY TABLE Volume_Information(
       ID INTEGER PRIMARY KEY,
       VI_Creation_Date TEXT
    );


    INSERT INTO Volume_Information(VI_Creation_Date) VALUES
    ('10/30/2015 2:28:30 AM'),
    ('2/13/2016 7:51:04 AM'),
    ('5/15/2016 12:06:24 PM'),
    ('10/7/2016 1:27:13 PM'),
    ('3/3/2017 1:7:3 PM'),
    ('10/1/2016 6:59:18 AM'),
    ('10/09/2016 11:27:13 PM');


    WITH DA(ID,DT) AS (
          SELECT ID, replace(replace(replace(replace(VI_Creation_Date,'
    ',':'),'/',':'),'AM','0'),'PM','12')||':'
            FROM Volume_Information
    ), DB(i, k, l, c, r) AS (
           SELECT DA.ID, 0, 1, DA.DT, -1
             FROM DA
         UNION ALL
           SELECT i, k+1, instr(c, ':'), substr(c, instr( c, ':' ) + 1),
                  CAST(trim(substr(c, 1, instr(c, ':') - 1)) AS INT)
             FROM DB
            WHERE l > 0
    ), DC(ID, YY, MM, DD, HH, NN, SS, AP) AS (
           SELECT DA.ID,
                  MAX(CASE WHEN k=3 THEN DB.r ELSE 0 END),
                  MAX(CASE WHEN k=1 THEN DB.r ELSE 0 END),
                  MAX(CASE WHEN k=2 THEN DB.r ELSE 0 END),
                  MAX(CASE WHEN k=4 THEN DB.r ELSE 0 END),
                  MAX(CASE WHEN k=5 THEN DB.r ELSE 0 END),
                  MAX(CASE WHEN k=6 THEN DB.r ELSE 0 END),
                  MAX(CASE WHEN k=7 THEN DB.r ELSE 0 END)
             FROM DA, DB
            WHERE DA.ID = DB.i AND DB.r >= 0 AND DB.k > 0
            GROUP BY DA.ID
    ), DD(ID, ISO_DT) AS (
           SELECT ID, YY||'-'||
              CASE WHEN MM > 9 THEN MM ELSE '0'||MM END||'-'||
              CASE WHEN DD > 9 THEN DD ELSE '0'||DD END||' '||
              CASE
                WHEN HH = 12 AND AP = 0 THEN '00'
                WHEN HH = 12 AND AP > 0 THEN AP
                WHEN HH + AP > 9 THEN HH + AP
                ELSE '0'||HH
              END||':'||
              CASE WHEN NN > 9 THEN NN ELSE '0'||NN END||':'||
              CASE WHEN SS > 9 THEN SS ELSE '0'||SS END
         FROM DC
    )
    SELECT VI.ID, VI.VI_Creation_Date, DD.ISO_DT,
    printf('%0.1f',julianday('now')-julianday(DD.ISO_DT)) AS DaysSince
       FROM Volume_Information AS VI
       JOIN DD ON DD.ID = VI.ID
      ORDER BY VI.ID
    ;

       --     VI.ID    | VI.VI_Creation_Date      |
    DD.ISO_DT             | DaysSince
       -- ------------ | ------------------------ |
    --------------------- | ---------
       --       1      | 10/30/2015 2:28:30 AM    | 2015-10-30
    02:28:30   |     530.4
       --       2      | 2/13/2016 7:51:04 AM     | 2016-02-13
    07:51:04   |     424.2
       --       3      | 5/15/2016 12:06:24 PM    | 2016-05-15
    12:06:24   |     332.0
       --       4      | 10/7/2016 1:27:13 PM     | 2016-10-07
    13:27:13   |     186.9
       --       5      | 3/3/2017 1:7:3 PM        | 2017-03-03
    13:07:03   |      39.9
       --       6      | 10/1/2016 6:59:18 AM     | 2016-10-01
    06:59:18   |     193.2
       --       7      | 10/09/2016 11:27:13 PM   | 2016-10-09
    23:27:13   |     184.5


    DROP TABLE Volume_Information;

       --   Script Stats: Total Script Execution Time:     0d 00h 00m
    and 00.022s
       --                 Total Script Query Time:         -- --- ---
    --- --.----
       --                 Total Database Rows Changed:     7
       --                 Total Virtual-Machine Steps:     6304
       --                 Last executed Item Index:        4
       --                 Last Script Error:
       --
    ------------------------------------------------------------------------------------------------

       -- 2017-04-12 13:43:15.881  |  [Success]    Script Success.
       -- 2017-04-12 13:43:15.881  |  [Success]    Transaction Rolled back.
       -- -------  DB-Engine Logs (Contains logged information from all
    DB connections during run)  ------
       -- [2017-04-12 13:43:15.843] APPLICATION : Script
    E:\Documents\SQLiteAutoScript.sql started at 13:43:15.843 on 12 April.
       -- [2017-04-12 13:43:15.881] ERROR (284) : automatic index on DB(i)
       --
    ================================================================================================






_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite - Interrogate Date/Time field Statement question

R Smith
In reply to this post by Ron Barnes

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite - Interrogate Date/Time field Statement question

Paul Sanderson
In reply to this post by Ron Barnes
Hi Ron

Your dates are still not 8601

with your dates above in a  table called dateplay and column named vi

select vi,
    julianday('now')  as now,
    julianday(substr(replace(vi, '/', '-'), 1, 10)) as jday,
    julianday('now')  - julianday(substr(replace(vi, '/', '-'), 1, 10)) as
diff from
dateplay

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 13:13, Ron Barnes <[hidden email]> 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?
>
> Thanks in advance,
>
> -Ron
>
> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of Jim Callahan
> Sent: Tuesday, April 11, 2017 11:22 PM
> To: SQLite mailing list <[hidden email]>
> Subject: Re: [sqlite] SQLite - Interrogate Date/Time field Statement
> question
>
> This code:
>
> SELECT ( substr('02/13/2016',7,4) || '-'
>       || substr('02/13/2016',1,2) || '-'
>       || substr('02/13/2016',4,2) ) ;
>
> yields
>
> 2016-02-13
>
> The above code, is dependent on fixed length strings (the leading zero) in
> other words '02/13/2016' and not '2/13/2016'.
>
> If you do not have fixed length date strings,  you would probably have to
> use globs or regular expressions.
>
> *glob(X,Y)*
>
> The glob(X,Y) function is equivalent to the expression "*Y GLOB X*". Note
> that the X and Y arguments are reversed in the glob() function relative to
> the infix GLOB <https://sqlite.org/lang_expr.html#glob> operator.
>
> https://sqlite.org/lang_corefunc.html#glob
>
>
> The REGEXP operator is a special syntax for the regexp() user function. No
> regexp() user function is defined by default and so use of the REGEXP
> operator will normally result in an error message. If an
> application-defined SQL function <https://sqlite.org/c3ref/
> create_function.html> named "regexp"
> is added at run-time, then the "*X* REGEXP *Y*" operator will be
> implemented as a call to "regexp(*Y*,*X*)".
>
> https://sqlite.org/lang_expr.html
>
>
> Type of regular expression needed:
> https://social.msdn.microsoft.com/Forums/en-US/7f38ee7b-
> 15e2-4e2c-8389-1266f496e4b2/regular-expression-to-get-
> date-format-from-string?forum=csharplanguage
>
> ​Jim Callahan
>
> On Tue, Apr 11, 2017 at 10:00 PM, Ron Barnes <[hidden email]> wrote:
>
> > Hi Jim,
> >
> > I could alter the program that populates the Date/Time Column to the
> > format you specify.  I'm trying real hard not to as that program has
> > been in use for many years and it would be a significant undertaking
> > to convert the program then convert the existing data.  Not saying I
> > won't do it as I'm at that point, just wondering if it's possible to
> avoid that route.
> >
> > If I converted the date/time field, would it be easier to create counts?
> >
> > If you could, would you be able to offer a sample Select statement I
> > can alter to fit my needs?
> >
> > Thank you very much for the reply!
> >
> > Side note, I'll be visiting Disney in July!
> >
> > Regards,
> >
> > -Ron
> >
> > -----Original Message-----
> > From: sqlite-users
> > [mailto:[hidden email]]
> > On Behalf Of Jim Callahan
> > Sent: Tuesday, April 11, 2017 9:15 PM
> > To: SQLite mailing list <[hidden email]>
> > Subject: Re: [sqlite] SQLite - Interrogate Date/Time field Statement
> > question
> >
> > Can you convert the dates to ISO 8601 date time format?
> > https://en.wikipedia.org/wiki/ISO_8601
> >
> > YYYY-MM-DD hh:mm:ss
> >
> > ISO date strings (when zero filled) are sortable which necessarily
> > includes comparable (Java speak).
> > By "zero filled" I mean for March you have "03" and not just "3".
> >
> > Then if you could generate/populate the boundary values in ISO format;
> > the comparisons would be straightforward and you could avoid the
> > julian date conversion.
> >
> > Another disadvantage of Julian dates are the different base years used
> > by applications including Unix, MS Access, MS Excel for Windows and MS
> > Excel for MacIntosh. Each application is internally consistent, but
> > the minute you exchange data between applications...
> > https://support.microsoft.com/en-us/help/214330/differences-
> > between-the-1900-and-the-1904-date-system-in-excel
> >
> > Your specification actually requires day counts; so you may need
> > Julian dates after all.
> >
> > Jim Callahan
> > Orlando, FL
> >
> >
> >
> > On Tue, Apr 11, 2017 at 7:24 PM, Ron Barnes <[hidden email]>
> wrote:
> >
> > > Hello all,
> > >
> > > To everyone who helped me before - thank you very much!
> > >
> > > I'm coding in Visual Basic .NET (Visual Studio 2015) Community.
> > >
> > > I have to count a Date/Time field and the problem is, this field
> > > contains data in a format I'm not sure can be counted.
> > >
> > > I need to count all the dates in the field but the dates are a
> > > combined Date and time in the format examples below.
> > > My goal is to use the current Date/time ('NOW') and calculate the
> > > time difference in days, from my DB Sourced field.
> > >
> > > I need to capture...
> > > Less than 1 month old
> > > 1 month old
> > > 2 months old
> > > 1 year old.....
> > > all the way to greater than 10 years old.
> > >
> > > Is this even possible in SQLite and if so, how would I go about doing
> it?
> > >
> > > I have been googling a few queries and come up blank.
> > >
> > > I try this code and differing combinations of it but it always
> > > returns NULL.
> > >
> > > SELECT CAST
> > > ((datetime(julianday(datetime('now'))) -
> > > JulianDay(VI_Creation_Date)) As
> > > Integer)
> > > FROM Volume_Information
> > >
> > > Here is what I have to work with.
> > >
> > > Table Name:
> > > Volume_Information
> > >
> > > Column name:
> > > VI_Creation_Date
> > >
> > > Date Format:
> > > MM/DD/CCYY HH:MM:SS AM/PM
> > >
> > > Examples:
> > >
> > > 10/30/2015 2:28:30 AM
> > > 2/13/2016 7:51:04 AM
> > > 5/15/2016 12:06:24 PM
> > > 10/7/2016 1:27:13 PM
> > >
> > > Any Help would be greatly appreciated,
> > >
> > > Thanks,
> > >
> > > -Ron
> > >
> > > _______________________________________________
> > > sqlite-users mailing list
> > > [hidden email]
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite - Interrogate Date/Time field Statement question

Paul Sanderson
Oops hit send too quickly

the replace function replaces / with - in your date string to make the ISO
8601 and substr just makes sure we use the date portion only.

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 13:37, Paul Sanderson <[hidden email]>
wrote:

> Hi Ron
>
> Your dates are still not 8601
>
> with your dates above in a  table called dateplay and column named vi
>
> select vi,
>     julianday('now')  as now,
>     julianday(substr(replace(vi, '/', '-'), 1, 10)) as jday,
>     julianday('now')  - julianday(substr(replace(vi, '/', '-'), 1, 10)) as
> diff from
> dateplay
>
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786 <+44%201326%20572786>
> 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 13:13, Ron Barnes <[hidden email]> 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?
>>
>> Thanks in advance,
>>
>> -Ron
>>
>> -----Original Message-----
>> From: sqlite-users [mailto:[hidden email]]
>> On Behalf Of Jim Callahan
>> Sent: Tuesday, April 11, 2017 11:22 PM
>> To: SQLite mailing list <[hidden email]>
>> Subject: Re: [sqlite] SQLite - Interrogate Date/Time field Statement
>> question
>>
>> This code:
>>
>> SELECT ( substr('02/13/2016',7,4) || '-'
>>       || substr('02/13/2016',1,2) || '-'
>>       || substr('02/13/2016',4,2) ) ;
>>
>> yields
>>
>> 2016-02-13
>>
>> The above code, is dependent on fixed length strings (the leading zero)
>> in other words '02/13/2016' and not '2/13/2016'.
>>
>> If you do not have fixed length date strings,  you would probably have to
>> use globs or regular expressions.
>>
>> *glob(X,Y)*
>>
>> The glob(X,Y) function is equivalent to the expression "*Y GLOB X*". Note
>> that the X and Y arguments are reversed in the glob() function relative to
>> the infix GLOB <https://sqlite.org/lang_expr.html#glob> operator.
>>
>> https://sqlite.org/lang_corefunc.html#glob
>>
>>
>> The REGEXP operator is a special syntax for the regexp() user function. No
>> regexp() user function is defined by default and so use of the REGEXP
>> operator will normally result in an error message. If an
>> application-defined SQL function <https://sqlite.org/c3ref/crea
>> te_function.html> named "regexp"
>> is added at run-time, then the "*X* REGEXP *Y*" operator will be
>> implemented as a call to "regexp(*Y*,*X*)".
>>
>> https://sqlite.org/lang_expr.html
>>
>>
>> Type of regular expression needed:
>> https://social.msdn.microsoft.com/Forums/en-US/7f38ee7b-15e2
>> -4e2c-8389-1266f496e4b2/regular-expression-to-get-date-
>> format-from-string?forum=csharplanguage
>>
>> ​Jim Callahan
>>
>> On Tue, Apr 11, 2017 at 10:00 PM, Ron Barnes <[hidden email]>
>> wrote:
>>
>> > Hi Jim,
>> >
>> > I could alter the program that populates the Date/Time Column to the
>> > format you specify.  I'm trying real hard not to as that program has
>> > been in use for many years and it would be a significant undertaking
>> > to convert the program then convert the existing data.  Not saying I
>> > won't do it as I'm at that point, just wondering if it's possible to
>> avoid that route.
>> >
>> > If I converted the date/time field, would it be easier to create counts?
>> >
>> > If you could, would you be able to offer a sample Select statement I
>> > can alter to fit my needs?
>> >
>> > Thank you very much for the reply!
>> >
>> > Side note, I'll be visiting Disney in July!
>> >
>> > Regards,
>> >
>> > -Ron
>> >
>> > -----Original Message-----
>> > From: sqlite-users
>> > [mailto:[hidden email]]
>> > On Behalf Of Jim Callahan
>> > Sent: Tuesday, April 11, 2017 9:15 PM
>> > To: SQLite mailing list <[hidden email]>
>> > Subject: Re: [sqlite] SQLite - Interrogate Date/Time field Statement
>> > question
>> >
>> > Can you convert the dates to ISO 8601 date time format?
>> > https://en.wikipedia.org/wiki/ISO_8601
>> >
>> > YYYY-MM-DD hh:mm:ss
>> >
>> > ISO date strings (when zero filled) are sortable which necessarily
>> > includes comparable (Java speak).
>> > By "zero filled" I mean for March you have "03" and not just "3".
>> >
>> > Then if you could generate/populate the boundary values in ISO format;
>> > the comparisons would be straightforward and you could avoid the
>> > julian date conversion.
>> >
>> > Another disadvantage of Julian dates are the different base years used
>> > by applications including Unix, MS Access, MS Excel for Windows and MS
>> > Excel for MacIntosh. Each application is internally consistent, but
>> > the minute you exchange data between applications...
>> > https://support.microsoft.com/en-us/help/214330/differences-
>> > between-the-1900-and-the-1904-date-system-in-excel
>> >
>> > Your specification actually requires day counts; so you may need
>> > Julian dates after all.
>> >
>> > Jim Callahan
>> > Orlando, FL
>> >
>> >
>> >
>> > On Tue, Apr 11, 2017 at 7:24 PM, Ron Barnes <[hidden email]>
>> wrote:
>> >
>> > > Hello all,
>> > >
>> > > To everyone who helped me before - thank you very much!
>> > >
>> > > I'm coding in Visual Basic .NET (Visual Studio 2015) Community.
>> > >
>> > > I have to count a Date/Time field and the problem is, this field
>> > > contains data in a format I'm not sure can be counted.
>> > >
>> > > I need to count all the dates in the field but the dates are a
>> > > combined Date and time in the format examples below.
>> > > My goal is to use the current Date/time ('NOW') and calculate the
>> > > time difference in days, from my DB Sourced field.
>> > >
>> > > I need to capture...
>> > > Less than 1 month old
>> > > 1 month old
>> > > 2 months old
>> > > 1 year old.....
>> > > all the way to greater than 10 years old.
>> > >
>> > > Is this even possible in SQLite and if so, how would I go about doing
>> it?
>> > >
>> > > I have been googling a few queries and come up blank.
>> > >
>> > > I try this code and differing combinations of it but it always
>> > > returns NULL.
>> > >
>> > > SELECT CAST
>> > > ((datetime(julianday(datetime('now'))) -
>> > > JulianDay(VI_Creation_Date)) As
>> > > Integer)
>> > > FROM Volume_Information
>> > >
>> > > Here is what I have to work with.
>> > >
>> > > Table Name:
>> > > Volume_Information
>> > >
>> > > Column name:
>> > > VI_Creation_Date
>> > >
>> > > Date Format:
>> > > MM/DD/CCYY HH:MM:SS AM/PM
>> > >
>> > > Examples:
>> > >
>> > > 10/30/2015 2:28:30 AM
>> > > 2/13/2016 7:51:04 AM
>> > > 5/15/2016 12:06:24 PM
>> > > 10/7/2016 1:27:13 PM
>> > >
>> > > Any Help would be greatly appreciated,
>> > >
>> > > Thanks,
>> > >
>> > > -Ron
>> > >
>> > > _______________________________________________
>> > > sqlite-users mailing list
>> > > [hidden email]
>> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> > >
>> > _______________________________________________
>> > sqlite-users mailing list
>> > [hidden email]
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> > _______________________________________________
>> > sqlite-users mailing list
>> > [hidden email]
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite - Interrogate Date/Time field Statement question

Ron Barnes
In reply to this post by R Smith
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:[hidden email]] On Behalf Of R Smith
Sent: Wednesday, April 12, 2017 8:32 AM
To: [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite - Interrogate Date/Time field Statement question

Paul Sanderson
Try something like

SELECT dateplay.vi,
  JulianDay('now') AS now,
  JulianDay(SubStr(Replace(dateplay.vi, '/', '-'), 1, 10)) AS jday,
  JulianDay('now') - JulianDay(SubStr(Replace(dateplay.vi, '/', '-'), 1,
10)) AS diff,
  CASE
    WHEN JulianDay('now') - JulianDay(SubStr(Replace(dateplay.vi, '/',
'-'), 1, 10)) < 1 THEN 'Under 1 day'
    WHEN JulianDay('now') - JulianDay(SubStr(Replace(dateplay.vi, '/',
'-'), 1, 10)) < 8 THEN 'Under 1 week'
    WHEN JulianDay('now') - JulianDay(SubStr(Replace(dateplay.vi, '/',
'-'), 1, 10)) < 32 THEN 'Under 1 month'
    WHEN JulianDay('now') - JulianDay(SubStr(Replace(dateplay.vi, '/',
'-'), 1, 10)) < 365 THEN 'Under 1 year'
    END AS category
FROM dateplay

there may well be a neater way of doing it :)


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 <[hidden email]> 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:[hidden email]]
> On Behalf Of R Smith
> Sent: Wednesday, April 12, 2017 8:32 AM
> To: [hidden email]
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite - Interrogate Date/Time field Statement question

Paul Sanderson
In reply to this post by Ron Barnes
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 <[hidden email]> 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:[hidden email]]
> On Behalf Of R Smith
> Sent: Wednesday, April 12, 2017 8:32 AM
> To: [hidden email]
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite - Interrogate Date/Time field Statement question

R Smith
In reply to this post by Ron Barnes
On 2017/04/12 3:08 PM, Ron Barnes 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

Nothing much wrong with the idea, but I suppose the syntax is not clear.

This works by me:

SELECT G.cat, COUNT(*) AS qty
   FROM (
     SELECT C.days, CASE
              WHEN C.days <    1 THEN '1. Under 1 Day'
              WHEN C.days <    7 THEN '2. Under 1 Week'
              WHEN C.days <   31 THEN '3. Under 1 Month'
              WHEN C.days <  366 THEN '4. Under 1 Year'
              WHEN C.days <  731 THEN '5. Under 2 Years'
              WHEN C.days < 1826 THEN '6. Under 5 Years'
              WHEN C.days < 3651 THEN '7. Under 10 Years'
              ELSE '8. Over 10 Years'
            END AS cat
       FROM (
              SELECT
julianday('now')-julianday(replace(substr(VI_Creation_Date,1,10),'/','-'))
AS days
            FROM Volume_Information
         ) AS C
     ) AS G
  GROUP BY G.cat
  ORDER BY G.cat
;


I took the liberty of fixing the cut-offs a bit to better reflect the
truth and added a number to the category so ordering would make sense.

Note that these figures are not cumulative - i.e. if there are  25 items
this month, of which 10 items for this week and 2 of them are in the
last day, then the results will show:
2 Under 1 Day
8 Under 1 Week
15 Under 1 Month
While, technically, there are 25 items for the month and 10 items under
the last week...

This may be exactly as you need, but if not, let me know then we can try
another way.

Cheers,
Ryan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite - Interrogate Date/Time field Statement question

R Smith
In reply to this post by Paul Sanderson

On 2017/04/12 3:50 PM, Paul Sanderson wrote:
> another oops I see Ryan pretty much posted the same as me 5 minutes earlier
> - I'll go back to bed :)

The more the merrier I say.
There is no guarantee I am right, and seeing the problem solved in more
than one way usually helps the poster  - which is better for us all.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite - Interrogate Date/Time field Statement question

Ron Barnes
In reply to this post by R Smith
That worked!!!!

Thank you very Much!!

-Ron

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of R Smith
Sent: Wednesday, April 12, 2017 10:12 AM
To: [hidden email]
Subject: Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

On 2017/04/12 3:08 PM, Ron Barnes 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

Nothing much wrong with the idea, but I suppose the syntax is not clear.

This works by me:

SELECT G.cat, COUNT(*) AS qty
   FROM (
     SELECT C.days, CASE
              WHEN C.days <    1 THEN '1. Under 1 Day'
              WHEN C.days <    7 THEN '2. Under 1 Week'
              WHEN C.days <   31 THEN '3. Under 1 Month'
              WHEN C.days <  366 THEN '4. Under 1 Year'
              WHEN C.days <  731 THEN '5. Under 2 Years'
              WHEN C.days < 1826 THEN '6. Under 5 Years'
              WHEN C.days < 3651 THEN '7. Under 10 Years'
              ELSE '8. Over 10 Years'
            END AS cat
       FROM (
              SELECT
julianday('now')-julianday(replace(substr(VI_Creation_Date,1,10),'/','-'))
AS days
            FROM Volume_Information
         ) AS C
     ) AS G
  GROUP BY G.cat
  ORDER BY G.cat
;


I took the liberty of fixing the cut-offs a bit to better reflect the truth and added a number to the category so ordering would make sense.

Note that these figures are not cumulative - i.e. if there are  25 items this month, of which 10 items for this week and 2 of them are in the last day, then the results will show:
2 Under 1 Day
8 Under 1 Week
15 Under 1 Month
While, technically, there are 25 items for the month and 10 items under the last week...

This may be exactly as you need, but if not, let me know then we can try another way.

Cheers,
Ryan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite - Interrogate Date/Time field Statement question

Kam YiJie
In reply to this post by Jim Callahan


STOP SPAMM ING ING ME  ASS FACE
________________________________
From: sqlite-users <[hidden email]> on behalf of Jim Callahan <[hidden email]>
Sent: Wednesday, April 12, 2017 11:21 AM
To: SQLite mailing list
Subject: Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

This code:

SELECT ( substr('02/13/2016',7,4) || '-'
      || substr('02/13/2016',1,2) || '-'
      || substr('02/13/2016',4,2) ) ;

yields

2016-02-13

The above code, is dependent on fixed length strings (the leading zero) in
other words '02/13/2016' and not '2/13/2016'.

If you do not have fixed length date strings,  you would probably have to
use
globs or regular expressions.

*glob(X,Y)*

The glob(X,Y) function is equivalent to the expression "*Y GLOB X*". Note
that the X and Y arguments are reversed in the glob() function relative to
the infix GLOB <https://sqlite.org/lang_expr.html#glob> operator.
[https://sqlite.org/images/syntax/expr.gif]<https://sqlite.org/lang_expr.html#glob>

SQLite Query Language: expression<https://sqlite.org/lang_expr.html#glob>
sqlite.org
If a numeric literal has a decimal point or an exponentiation clause or if its magnitude is less than -9223372036854775808 or greater than 9223372036854775807, then ...




https://sqlite.org/lang_corefunc.html#glob
SQLite Query Language: Core Functions<https://sqlite.org/lang_corefunc.html#glob>
sqlite.org
The core functions shown below are available by default. Date & Time functions, aggregate functions, and JSON functions are documented separately.





The REGEXP operator is a special syntax for the regexp() user function. No
regexp() user function is defined by default and so use of the REGEXP
operator will normally result in an error message. If an application-defined
SQL function <https://sqlite.org/c3ref/create_function.html> named "regexp"
is added at run-time, then the "*X* REGEXP *Y*" operator will be
implemented as a call to "regexp(*Y*,*X*)".

https://sqlite.org/lang_expr.html


Type of regular expression needed:
https://social.msdn.microsoft.com/Forums/en-US/7f38ee7b-15e2-4e2c-8389-1266f496e4b2/regular-expression-to-get-date-format-from-string?forum=csharplanguage

Jim Callahan

On Tue, Apr 11, 2017 at 10:00 PM, Ron Barnes <[hidden email]> wrote:

> Hi Jim,
>
> I could alter the program that populates the Date/Time Column to the
> format you specify.  I'm trying real hard not to as that program has been
> in use for many years and it would be a significant undertaking to convert
> the program then convert the existing data.  Not saying I won't do it as
> I'm at that point, just wondering if it's possible to avoid that route.
>
> If I converted the date/time field, would it be easier to create counts?
>
> If you could, would you be able to offer a sample Select statement I can
> alter to fit my needs?
>
> Thank you very much for the reply!
>
> Side note, I'll be visiting Disney in July!
>
> Regards,
>
> -Ron
>
> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of Jim Callahan
> Sent: Tuesday, April 11, 2017 9:15 PM
> To: SQLite mailing list <[hidden email]>
> Subject: Re: [sqlite] SQLite - Interrogate Date/Time field Statement
> question
>
> Can you convert the dates to ISO 8601 date time format?
> https://en.wikipedia.org/wiki/ISO_8601
>
> YYYY-MM-DD hh:mm:ss
>
> ISO date strings (when zero filled) are sortable which necessarily
> includes comparable (Java speak).
> By "zero filled" I mean for March you have "03" and not just "3".
>
> Then if you could generate/populate the boundary values in ISO format; the
> comparisons would be straightforward and you could avoid the julian date
> conversion.
>
> Another disadvantage of Julian dates are the different base years used by
> applications including Unix, MS Access, MS Excel for Windows and MS Excel
> for MacIntosh. Each application is internally consistent, but the minute
> you exchange data between applications...
> https://support.microsoft.com/en-us/help/214330/differences-
> between-the-1900-and-the-1904-date-system-in-excel
>
> Your specification actually requires day counts; so you may need Julian
> dates after all.
>
> Jim Callahan
> Orlando, FL
>
>
>
> On Tue, Apr 11, 2017 at 7:24 PM, Ron Barnes <[hidden email]> wrote:
>
> > Hello all,
> >
> > To everyone who helped me before - thank you very much!
> >
> > I'm coding in Visual Basic .NET (Visual Studio 2015) Community.
> >
> > I have to count a Date/Time field and the problem is, this field
> > contains data in a format I'm not sure can be counted.
> >
> > I need to count all the dates in the field but the dates are a
> > combined Date and time in the format examples below.
> > My goal is to use the current Date/time ('NOW') and calculate the time
> > difference in days, from my DB Sourced field.
> >
> > I need to capture...
> > Less than 1 month old
> > 1 month old
> > 2 months old
> > 1 year old.....
> > all the way to greater than 10 years old.
> >
> > Is this even possible in SQLite and if so, how would I go about doing it?
> >
> > I have been googling a few queries and come up blank.
> >
> > I try this code and differing combinations of it but it always returns
> > NULL.
> >
> > SELECT CAST
> > ((datetime(julianday(datetime('now'))) - JulianDay(VI_Creation_Date))
> > As
> > Integer)
> > FROM Volume_Information
> >
> > Here is what I have to work with.
> >
> > Table Name:
> > Volume_Information
> >
> > Column name:
> > VI_Creation_Date
> >
> > Date Format:
> > MM/DD/CCYY HH:MM:SS AM/PM
> >
> > Examples:
> >
> > 10/30/2015 2:28:30 AM
> > 2/13/2016 7:51:04 AM
> > 5/15/2016 12:06:24 PM
> > 10/7/2016 1:27:13 PM
> >
> > Any Help would be greatly appreciated,
> >
> > Thanks,
> >
> > -Ron
> >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite - Interrogate Date/Time field Statement question

Ron Barnes
Hi Jim,

I was able to get the SQL Working with this code From Ryan via the Mailing List.  Thank you for all your help!

SELECT        cat, COUNT(*) AS qty
FROM            (SELECT  days, CASE
WHEN C.days < 1 THEN 'Under 1 Day'
WHEN C.days < 7 THEN 'Under 1 Week'
WHEN C.days < 31 THEN 'Under 1 Month'
WHEN C.days < 366 THEN 'Under 1 Year'
WHEN C.days < 731 THEN 'Under 2 Years'
WHEN C.days < 1826 THEN 'Under 5 Years'
WHEN C.days < 3651 THEN 'Under 10 Years'
 ELSE 'Over 10 Years' END AS cat
 FROM            (SELECT   julianday('now') - julianday([REPLACE](substr(VI_Creation_Date, 1, 10), '/', '-')) AS days
  FROM            Volume_Information) C) G
GROUP BY cat
ORDER BY cat

Works great BTW

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Kam YiJie
Sent: Friday, April 14, 2017 6:37 AM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] SQLite - Interrogate Date/Time field Statement question



STOP SPAMM ING ING ME  ASS FACE
________________________________
From: sqlite-users <[hidden email]> on behalf of Jim Callahan <[hidden email]>
Sent: Wednesday, April 12, 2017 11:21 AM
To: SQLite mailing list
Subject: Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

This code:

SELECT ( substr('02/13/2016',7,4) || '-'
      || substr('02/13/2016',1,2) || '-'
      || substr('02/13/2016',4,2) ) ;

yields

2016-02-13

The above code, is dependent on fixed length strings (the leading zero) in other words '02/13/2016' and not '2/13/2016'.

If you do not have fixed length date strings,  you would probably have to use globs or regular expressions.

*glob(X,Y)*

The glob(X,Y) function is equivalent to the expression "*Y GLOB X*". Note that the X and Y arguments are reversed in the glob() function relative to the infix GLOB <https://sqlite.org/lang_expr.html#glob> operator.
[https://sqlite.org/images/syntax/expr.gif]<https://sqlite.org/lang_expr.html#glob>

SQLite Query Language: expression<https://sqlite.org/lang_expr.html#glob>
sqlite.org
If a numeric literal has a decimal point or an exponentiation clause or if its magnitude is less than -9223372036854775808 or greater than 9223372036854775807, then ...




https://sqlite.org/lang_corefunc.html#glob
SQLite Query Language: Core Functions<https://sqlite.org/lang_corefunc.html#glob>
sqlite.org
The core functions shown below are available by default. Date & Time functions, aggregate functions, and JSON functions are documented separately.





The REGEXP operator is a special syntax for the regexp() user function. No
regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message. If an application-defined SQL function <https://sqlite.org/c3ref/create_function.html> named "regexp"
is added at run-time, then the "*X* REGEXP *Y*" operator will be implemented as a call to "regexp(*Y*,*X*)".

https://sqlite.org/lang_expr.html


Type of regular expression needed:
https://social.msdn.microsoft.com/Forums/en-US/7f38ee7b-15e2-4e2c-8389-1266f496e4b2/regular-expression-to-get-date-format-from-string?forum=csharplanguage

Jim Callahan

On Tue, Apr 11, 2017 at 10:00 PM, Ron Barnes <[hidden email]> wrote:

> Hi Jim,
>
> I could alter the program that populates the Date/Time Column to the
> format you specify.  I'm trying real hard not to as that program has
> been in use for many years and it would be a significant undertaking
> to convert the program then convert the existing data.  Not saying I
> won't do it as I'm at that point, just wondering if it's possible to avoid that route.
>
> If I converted the date/time field, would it be easier to create counts?
>
> If you could, would you be able to offer a sample Select statement I
> can alter to fit my needs?
>
> Thank you very much for the reply!
>
> Side note, I'll be visiting Disney in July!
>
> Regards,
>
> -Ron
>
> -----Original Message-----
> From: sqlite-users
> [mailto:[hidden email]]
> On Behalf Of Jim Callahan
> Sent: Tuesday, April 11, 2017 9:15 PM
> To: SQLite mailing list <[hidden email]>
> Subject: Re: [sqlite] SQLite - Interrogate Date/Time field Statement
> question
>
> Can you convert the dates to ISO 8601 date time format?
> https://en.wikipedia.org/wiki/ISO_8601
>
> YYYY-MM-DD hh:mm:ss
>
> ISO date strings (when zero filled) are sortable which necessarily
> includes comparable (Java speak).
> By "zero filled" I mean for March you have "03" and not just "3".
>
> Then if you could generate/populate the boundary values in ISO format;
> the comparisons would be straightforward and you could avoid the
> julian date conversion.
>
> Another disadvantage of Julian dates are the different base years used
> by applications including Unix, MS Access, MS Excel for Windows and MS
> Excel for MacIntosh. Each application is internally consistent, but
> the minute you exchange data between applications...
> https://support.microsoft.com/en-us/help/214330/differences-
> between-the-1900-and-the-1904-date-system-in-excel
>
> Your specification actually requires day counts; so you may need
> Julian dates after all.
>
> Jim Callahan
> Orlando, FL
>
>
>
> On Tue, Apr 11, 2017 at 7:24 PM, Ron Barnes <[hidden email]> wrote:
>
> > Hello all,
> >
> > To everyone who helped me before - thank you very much!
> >
> > I'm coding in Visual Basic .NET (Visual Studio 2015) Community.
> >
> > I have to count a Date/Time field and the problem is, this field
> > contains data in a format I'm not sure can be counted.
> >
> > I need to count all the dates in the field but the dates are a
> > combined Date and time in the format examples below.
> > My goal is to use the current Date/time ('NOW') and calculate the
> > time difference in days, from my DB Sourced field.
> >
> > I need to capture...
> > Less than 1 month old
> > 1 month old
> > 2 months old
> > 1 year old.....
> > all the way to greater than 10 years old.
> >
> > Is this even possible in SQLite and if so, how would I go about doing it?
> >
> > I have been googling a few queries and come up blank.
> >
> > I try this code and differing combinations of it but it always
> > returns NULL.
> >
> > SELECT CAST
> > ((datetime(julianday(datetime('now'))) -
> > JulianDay(VI_Creation_Date)) As
> > Integer)
> > FROM Volume_Information
> >
> > Here is what I have to work with.
> >
> > Table Name:
> > Volume_Information
> >
> > Column name:
> > VI_Creation_Date
> >
> > Date Format:
> > MM/DD/CCYY HH:MM:SS AM/PM
> >
> > Examples:
> >
> > 10/30/2015 2:28:30 AM
> > 2/13/2016 7:51:04 AM
> > 5/15/2016 12:06:24 PM
> > 10/7/2016 1:27:13 PM
> >
> > Any Help would be greatly appreciated,
> >
> > Thanks,
> >
> > -Ron
> >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users