[sqlite] Get rid of old-style function declarations/definitions in lemon.c

[Guy Harris]

Here's a patch, against the current Fossil repository, including some changes 
we've made to lemon.c in Wireshark, that gets rid of old-style K function 
definitions/declarations, and also removes trailing white space from some lines 
(the Wireshark pre-commit hook complains about them):

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Issue with LoadExtension with Spatialite

[Green Fields]

I have had this problem. Essentially, precompiled binaries (SQLite3.dll,
and System.Data.Sqlite) for Windows do not appear to allow extension
loading using the SQL function.
I have raised this before,
http://sqlite.1065341.n5.nabble.com/Extension-Loading-td93885.html but it
may need custom compiling although I'm not sure what flags would be
required.
The Windows CLI does allow loading by SQL function
I didn't find a resolution here or at Spatialite




>Have you guys seen this?
>I think this is an issue with System.Data.SQLite for C#.
>I'm having the same issue.
>https://groups.google.com/forum/#!topic/spatialite-users/u2QZpQL_6ek
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ambiguity in 'pragma page_size' docs

[Richard Hipp]

On 4/12/17, David Raymond  wrote:

>
> When I open a connection, what's
> the best way to ensure I've connected to an existing file, and am not about
> to create a brand new one by trying to find out?

Use sqlite3_open_v2() to create the database connection and make sure the
flag parameter is SQLITE_OPEN_READWRITE or SQLITE_OPEN_READONLY and
that it does not include the SQLITE_OPEN_CREATE bit.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ambiguity in 'pragma page_size' docs

[David Raymond]

I think there're two questions going on here. (Though I'm probably adding on to 
the original question)

First is similar to a recent thread and is: When I open a connection, what's 
the best way to ensure I've connected to an existing file, and am not about to 
create a brand new one by trying to find out? (In SQLite and apart from 
external programming language file system commands anyway)

The other one is: What do I need to explicitly set, and in what order when I'm 
making a new database? For me, for example, I use SQLite in Python, so while I 
know what's on my own computer, when I hand off the scripts to the world at 
large who knows what version or compilation options their Python installations' 
sqlite3.dll has in it. So of page_size, auto_vacuum, legacy_file_format, 
encoding, journal_mode, etc. what pragmas do I need to set and in what order to 
make sure it creates what I want? For example as stated, if you do journal_mode 
= wal before you set page_size, then you may not notice that page_size didn't 
get set because the file had been created at the journal_mode change.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Wednesday, April 12, 2017 3:53 PM
To: SQLite mailing list
Subject: Re: [sqlite] Ambiguity in 'pragma page_size' docs

On 4/12/17, Jens Alfke  wrote:
> The docs[1] say that:
>
>> The page_size pragma will only set in the page size if it is issued before
>> any other SQL statements that cause I/O against the database file.
>
> It's not stated explicitly, but I believe the pragma has to be issued before
> any other statement _ever_ causes I/O, i.e. it has to be the very first
> persistent statement ever used on a new database file. (Except for the
> situation regarding VACUUM described later on.) Is this true?

I think the best thing to do is:  "PRAGMA page_size=4096; VACUUM;"  --
all in a single call to sqlite3_exec().  But only do this when you
really need to change the page size as it is expensive.

To a first approximation, you never need to change the page size.  So
why is this important to you?

>
>> SQL statements that cause I/O against the database file include "CREATE",
>> "SELECT", "BEGIN IMMEDIATE", and "PRAGMA journal_mode=WAL".
>
> I'm guessing that "PRAGMA user_version" also causes I/O.
>

Yes

>
> The implication is
> that, if you want to set the page_size, you have to do it every time the
> database is opened, not just on first-time initialization; because checking
> to see if you've initialized the database already would trigger I/O which
> will make setting the page_size a no-op. (Unless you use the filesystem to
> check that the db file is missing or empty before opening it, I suppose.)
>

The implication *should* be that you *never* change the page size.
SQLite will pick a good page size for you automatically, which works
in 99.99% of all cases.

In the exceptional case where you do need to adjust the page_size
manually, do it exactly once, as if you are running CREATE INDEX.
-- 
D. Richard Hipp
d...@sqlite.org
___
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

Re: [sqlite] Ambiguity in 'pragma page_size' docs

[Jens Alfke]

> On Apr 12, 2017, at 12:53 PM, Richard Hipp  wrote:
> 
> The implication *should* be that you *never* change the page size.
> SQLite will pick a good page size for you automatically, which works
> in 99.99% of all cases.

… provided you're running 3.12 or later. Prior to that it would pick 1024, 
which the docs suggest is non-optimal. (I'm sure it's not a big difference, but 
if I can glean some performance by adding one line of code, hey, I'm doing it.)

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Which pragmas are persistent?

[Jens Alfke]

> On Apr 12, 2017, at 1:01 PM, Simon Slavin  wrote:
> 
>> which I only need to issue when initializing a new database.
> 
> 'need' is a bit strong.  I have never worried about pagesize in a database.  
> I’ve always just left it at the default for whatever platform I’m using when 
> I create the database.  But I don’t write programs where SQLite speed is a 
> bottleneck.

I didn't mean 'need' quite so strongly; it was more like "…which I don't need 
to issue when reopening an existing database".

The discussion of the page-size change in 3.12.0 says that "on modern hardware, 
a 4096 byte page is a faster and better choice", and performance is important 
to our code, so it seemed worth it to bump the page size. (We still support 
SQLite versions older than 3.12.)

> I have previous argued for category documention for PRAGMAs: which ones are 
> stored in the database, which ones just tell you things, etc..

Yes, that would be great.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Which pragmas are persistent?

[David Raymond]

I recommend taking a look at
http://www.sqlite.org/fileformat2.html
and look at 1.2 The Database Header. Since everything's stored in the file, the 
permanent pragmas are going to be ones which change one of the values in there.



Permanent:
page_size (change requires vacuum)
auto_vacuum (change to or from incremental requires vacuum)
journal_mode (only WAL vs non-WAL)
legacy_file_format (think can only be changed at database creation)
encoding (think can only be changed at database creation)
user_version

Connection lifespan only:
(Also note this means any of these will not affect any other connections)
foreign_keys (biggest one to remember I think)
journal_mode (only if between the non-WAL types)
synchronous
automatic_index
busy_timeout
cache_size
case_sensitive_like
defer_foreign_keys
ignore_check_constraints
locking_mode
max_page_count
mmap_size
recursive_triggers
reverse_unordered_selects
etc, etc...

Please correct me if I messed up.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Jens Alfke
Sent: Wednesday, April 12, 2017 3:29 PM
To: SQLite mailing list
Subject: [sqlite] Which pragmas are persistent?

Many of SQLite's pragma commands change database settings. It would be helpful 
if their documentation[1] stated which of these persist across 
closing/reopening the database, and which are scoped only to the open 
connection. For example, the docs say that that 'pragma journal_mode=WAL' is 
persistent. Presumably 'mmap_size' is not because it doesn't have any effect on 
file storage. But there are some I'm unsure about:

auto_vacuum, page_size — Persistent?
journal_size_limit, synchronous — Ephemeral?

I'm asking because I'd like to know which pragmas I need to reissue every time 
the database connection is opened, and which I only need to issue when 
initializing a new database.

—Jens

[1]: http://www.sqlite.org/pragma.html
___
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

Re: [sqlite] Which pragmas are persistent?

[Simon Slavin]

On 12 Apr 2017, at 8:29pm, Jens Alfke  wrote:

> which I only need to issue when initializing a new database.

'need' is a bit strong.  I have never worried about pagesize in a database.  
I’ve always just left it at the default for whatever platform I’m using when I 
create the database.  But I don’t write programs where SQLite speed is a 
bottleneck.

I have previous argued for category documention for PRAGMAs: which ones are 
stored in the database, which ones just tell you things, etc..

The only pragma needed anew for each connection to the database is

PRAGMA busy_timeout = milliseconds

I might argue that given what it does, this should really be saved in the 
database file somewhere.

The rest

* just tell you things, have no lasting effect
* are single-use tools which change the database file in a one-time way
* are saved in the database file (e.g. journal mode = WAL, page size)
* can reasonably be different for different concurrent connections to the 
database (some poll for changes, others log changes)

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ambiguity in 'pragma page_size' docs

[Richard Hipp]

On 4/12/17, Jens Alfke  wrote:
> The docs[1] say that:
>
>> The page_size pragma will only set in the page size if it is issued before
>> any other SQL statements that cause I/O against the database file.
>
> It's not stated explicitly, but I believe the pragma has to be issued before
> any other statement _ever_ causes I/O, i.e. it has to be the very first
> persistent statement ever used on a new database file. (Except for the
> situation regarding VACUUM described later on.) Is this true?

I think the best thing to do is:  "PRAGMA page_size=4096; VACUUM;"  --
all in a single call to sqlite3_exec().  But only do this when you
really need to change the page size as it is expensive.

To a first approximation, you never need to change the page size.  So
why is this important to you?

>
>> SQL statements that cause I/O against the database file include "CREATE",
>> "SELECT", "BEGIN IMMEDIATE", and "PRAGMA journal_mode=WAL".
>
> I'm guessing that "PRAGMA user_version" also causes I/O.
>

Yes

>
> The implication is
> that, if you want to set the page_size, you have to do it every time the
> database is opened, not just on first-time initialization; because checking
> to see if you've initialized the database already would trigger I/O which
> will make setting the page_size a no-op. (Unless you use the filesystem to
> check that the db file is missing or empty before opening it, I suppose.)
>

The implication *should* be that you *never* change the page size.
SQLite will pick a good page size for you automatically, which works
in 99.99% of all cases.

In the exceptional case where you do need to adjust the page_size
manually, do it exactly once, as if you are running CREATE INDEX.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Regression in json()

[Rolf Ade]

The fix http://www.sqlite.org/cgi/src/info/4f1b5229a3bbc9d4 (Escape
control characters in JSON) introduced a regression.

> ./sqlite3 
SQLite version 3.19.0 2017-04-12 17:50:12
[...]
sqlite> select json('"ä"');
Error: malformed JSON

I'd expect: "ä"

The reason is

http://www.sqlite.org/cgi/src/artifact/18d80526c34e3eab?ln=804

The variable c is declared as char
(http://www.sqlite.org/cgi/src/artifact/18d80526c34e3eab?ln=736)

If I'm not mistaken that should be:

unsigned char c;
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Ambiguity in 'pragma page_size' docs

[Jens Alfke]

The docs[1] say that:

> The page_size pragma will only set in the page size if it is issued before 
> any other SQL statements that cause I/O against the database file.

It's not stated explicitly, but I believe the pragma has to be issued before 
any other statement _ever_ causes I/O, i.e. it has to be the very first 
persistent statement ever used on a new database file. (Except for the 
situation regarding VACUUM described later on.) Is this true?

(Also, the word "in" is a typo that should be removed from that sentence.)

> SQL statements that cause I/O against the database file include "CREATE", 
> "SELECT", "BEGIN IMMEDIATE", and "PRAGMA journal_mode=WAL".

I'm guessing that "PRAGMA user_version" also causes I/O. The implication is 
that, if you want to set the page_size, you have to do it every time the 
database is opened, not just on first-time initialization; because checking to 
see if you've initialized the database already would trigger I/O which will 
make setting the page_size a no-op. (Unless you use the filesystem to check 
that the db file is missing or empty before opening it, I suppose.)

—Jens

[1]: http://www.sqlite.org/pragma.html#pragma_page_size
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Which pragmas are persistent?

[Richard Hipp]

On 4/12/17, Jens Alfke  wrote:
> Many of SQLite's pragma commands change database settings. It would be
> helpful if their documentation[1] stated which of these persist across
> closing/reopening the database, and which are scoped only to the open
> connection. For example, the docs say that that 'pragma journal_mode=WAL' is
> persistent. Presumably 'mmap_size' is not because it doesn't have any effect
> on file storage. But there are some I'm unsure about:
>
> auto_vacuum, page_size — Persistent?
> journal_size_limit, synchronous — Ephemeral?

Yes.  Which other PRAGMAs are you interested in?

>
> I'm asking because I'd like to know which pragmas I need to reissue every
> time the database connection is opened, and which I only need to issue when
> initializing a new database.
>
> —Jens
>
> [1]: http://www.sqlite.org/pragma.html
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Which pragmas are persistent?

[Jens Alfke]

Many of SQLite's pragma commands change database settings. It would be helpful 
if their documentation[1] stated which of these persist across 
closing/reopening the database, and which are scoped only to the open 
connection. For example, the docs say that that 'pragma journal_mode=WAL' is 
persistent. Presumably 'mmap_size' is not because it doesn't have any effect on 
file storage. But there are some I'm unsure about:

auto_vacuum, page_size — Persistent?
journal_size_limit, synchronous — Ephemeral?

I'm asking because I'd like to know which pragmas I need to reissue every time 
the database connection is opened, and which I only need to issue when 
initializing a new database.

—Jens

[1]: http://www.sqlite.org/pragma.html
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

[Ron Barnes]

That worked

Thank you very Much!!

-Ron

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of R Smith
Sent: Wednesday, April 12, 2017 10:12 AM
To: sqlite-users@mailinglists.sqlite.org
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?
>
> SELECTcategory, 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
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

Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

[R Smith]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

[R Smith]

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?

SELECTcategory, 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

[Paul Sanderson]

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  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?
>
> SELECTcategory, 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

Re: [sqlite] 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  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?
>
> SELECTcategory, 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

Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

[Ron Barnes]

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?

SELECTcategory, 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

Re: [sqlite] 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 
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  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:sqlite-users-boun...@mailinglists.sqlite.org]
>> On Behalf Of Jim Callahan
>> Sent: Tuesday, April 11, 2017 11:22 PM
>> 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  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 > 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 
>> 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:sqlite-users-boun...@mailinglists.sqlite.org]
>> > On Behalf Of Jim Callahan
>> > Sent: Tuesday, April 11, 2017 9:15 PM
>> > To: SQLite mailing list 
>> > 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
>> >
>> > -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 

Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

[Paul Sanderson]

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  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:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Jim Callahan
> Sent: Tuesday, April 11, 2017 11:22 PM
> 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  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  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  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:sqlite-users-boun...@mailinglists.sqlite.org]
> > On Behalf Of Jim Callahan
> > Sent: Tuesday, April 11, 2017 9:15 PM
> > To: SQLite mailing list 
> > 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
> >
> > -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 
> wrote:
> >
> 

Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

[R Smith]

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

Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

[Ron Barnes]

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:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of R Smith
Sent: Wednesday, April 12, 2017 7:49 AM
To: sqlite-users@mailinglists.sqlite.org
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  | 

Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

[Ron Barnes]

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:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Jim Callahan
Sent: Tuesday, April 11, 2017 11:22 PM
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  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  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  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:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Jim Callahan
> Sent: Tuesday, April 11, 2017 9:15 PM
> To: SQLite mailing list 
> 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
>
> -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  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 

Re: [sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

[R Smith]

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: -- --- ---
   --- --.