Re: [sqlite] new Error database disk image is malformed

[Ron Barnes]

I'm coding that now.  I never thought about it.  Thank you!

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Jens Alfke
Sent: Friday, January 5, 2018 1:34 PM
To: SQLite mailing list 
Subject: Re: [sqlite] new Error database disk image is malformed



> On Jan 4, 2018, at 6:29 PM, Peter Da Silva  
> wrote:
> 
> Since you're I/O bound on socket connections, and not CPU or database bound, 
> you might want to just have one database thread that communicates using 
> native inter-thread messaging to pass out work and accept responses from the 
> worker threads.

+1. Also, this will make it easy to batch multiple updates into a single 
transaction, which greatly improves write performance.

—Jens

___
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] new Error database disk image is malformed

[Ron Barnes]

Hi Jens,

Thank you for the response.  
I left it at default so it should be a 1.  The threads are not I/O.  They each 
execute only a few short lines of code to gather network information and then 
hit the DB for a read using the information they just gathered to populate a 
key.  After the read they add the formatted data to a table that is eventually 
displayed to the user.

A quick overview of the code.
I check 64K ports for a response to determine if they are open or not (Nothing 
nefarious).  I use 30+ threads because it could take up to 10 seconds for a 
port to respond while other ports respond immediately.

If you are curious to see the app in action and get a better picture of what 
I'm doing.  It's on the Microsoft store for free.

Do a search of the MS Store for an App named Lo and Behold.

Or click this link..

https://www.microsoft.com/en-us/store/p/lo-and-behold/9nblggh533kc

-Ron

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Jens Alfke
Sent: Thursday, January 4, 2018 8:03 PM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] new Error database disk image is malformed



> On Jan 4, 2018, at 4:51 PM, Ron Barnes <rbar...@njdevils.net> wrote:
> 
> I hope not since I use synclock in my code when ever a thread is attempting a 
> write to the database.  That seems like the only issue from that page that I 
> may be doing.  I could have up to 30 or more threads reading from the DB but 
> only one to three active threads writing.

It depends on the value of SQLITE_THREADSAFE that SQLite was compiled with. 
(Check the docs for details.) If it’s set to 1, you can do what you’re doing. 
Otherwise, you cannot use a single SQLite connection on multiple threads 
without using your own mutex (if it’s 2), or at all (if it’s 0).

30 threads sounds like overkill, BTW. Generally the appropriate number is equal 
to the number of CPU cores. And if the task is I/O-bound there’s not much 
benefit to having more than one since only one of them can be inside SQLite at 
a time.

—Jens
___
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] new Error database disk image is malformed

[Ron Barnes]

Hi,

Ok. I've read the Document and I think I may be having a rouge thread issue.  I 
hope not since I use synclock in my code when ever a thread is attempting a 
write to the database.  That seems like the only issue from that page that I 
may be doing.  I could have up to 30 or more threads reading from the DB but 
only one to three active threads writing.  The way my code is set up, though is 
that each thread would have to wait for the previous thread to finish writing 
before its turn to write.  

Each process works as follows...

Gather information.
Format the information

Enter the write routine using synclock (Visual Basic)

Write the record
Exit the write routine.

I'm using Visual Studio 2015
Visual Basic
Samsung 2TB SSD
64GB RAM
8 Core AMD Processor

Could any of the 30 or so concurrent reads be messing up my writes?

Here is my Create string...

Dim CreateNewDBConString As String = "Data Source=" + Trim(MyPath) + 
"\LoAndBehold.DB3;Version=3;New=True;Max Page 
Count=10485760;Compress=True;journal_mode=WAL;"

Here is my read/write string...

Dim Myconstring As String = "Data Source=" + Trim(MyPath) + 
"\LoAndBehold.DB3;Version=3;New=False;Max Page 
Count=10485760;Compress=True;journal_mode=WAL;"


Regards,

-Ron

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of R Smith
Sent: Thursday, January 4, 2018 3:14 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] new Error database disk image is malformed


On 2018/01/04 9:49 PM, Ron Barnes wrote:
> Hi All,
>
> I keep generating this error and I can't figure out why.  I have deleted and 
> re-created the database but it keeps popping up.
>
> Error
>
> database disk image is malformed
>
>
> Any ideas why?

This is a wild guess, but I'm thinking it's because the disk image is 
malformed...? :)

More seriously, what you probably meant to ask is: "Any idea why my file gets 
corrupt in this way?"

To which the best answer is: No idea, but here are some things that are typical 
culprits:
https://sqlite.org/howtocorrupt.html

Once you've checked all those, and you still are not sure what could cause the 
corruption, please write again but then include all information, OS, storage 
media, file system, use case, etc. and likely someone here would have had 
similar experience or run a similar system (or perhaps see some other obvious 
problem).

Good luck!
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] new Error database disk image is malformed

[Ron Barnes]

Thank you - I will and report back!

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of R Smith
Sent: Thursday, January 4, 2018 3:14 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] new Error database disk image is malformed


On 2018/01/04 9:49 PM, Ron Barnes wrote:
> Hi All,
>
> I keep generating this error and I can't figure out why.  I have deleted and 
> re-created the database but it keeps popping up.
>
> Error
>
> database disk image is malformed
>
>
> Any ideas why?

This is a wild guess, but I'm thinking it's because the disk image is 
malformed...? :)

More seriously, what you probably meant to ask is: "Any idea why my file gets 
corrupt in this way?"

To which the best answer is: No idea, but here are some things that are typical 
culprits:
https://sqlite.org/howtocorrupt.html

Once you've checked all those, and you still are not sure what could cause the 
corruption, please write again but then include all information, OS, storage 
media, file system, use case, etc. and likely someone here would have had 
similar experience or run a similar system (or perhaps see some other obvious 
problem).

Good luck!
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] new Error database disk image is malformed

[Ron Barnes]

Hi All,

I keep generating this error and I can't figure out why.  I have deleted and 
re-created the database but it keeps popping up.

Error

database disk image is malformed


Any ideas why?

-Ron


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

Re: [sqlite] Is there a way to perform a muti-level sort and extract of large data sets?

[Ron Barnes]



Than you!

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Stephen Chrzanowski
Sent: Tuesday, September 26, 2017 4:31 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Is there a way to perform a muti-level sort and extract 
of large data sets?

uh.. UNIQUE... DISTINCT... ,

On Tue, Sep 26, 2017 at 4:30 PM, Stephen Chrzanowski 
wrote:

>
>
> On Tue, Sep 26, 2017 at 1:36 PM, Simon Slavin 
> wrote:
>
>>
>>
>> My one concern in reading your post is how your dates are formatted.
>> When putting your date fields into your SQL table you will have to 
>> ensure that dates are saved as a day number, or as text which 
>> naturally sorts into date order, e.g. /DD/MM.  You should not 
>> expect SQL to sort text such as "19 October 16" correctly.
>>
>> Simon.
>>
>
> @OP, Simon is dead on, however, the only correction and clarification 
> to that statement is you'll want (if required) to sort by /MM/DD, 
> not /DD/MM.
>
> Also, for deduplication, if you're executing one instruction, you can 
> add a UNIQUE clause after the SELECT, unless you've got other 
> requirements that make defines what a duplicate entry is, then you'd 
> have to rely on your software for those decisions.
>
___
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] Is there a way to perform a muti-level sort and extract of large data sets?

[Ron Barnes]

That is exactly what I want to do

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Keith Medcalf
Sent: Tuesday, September 26, 2017 3:04 PM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Is there a way to perform a muti-level sort and extract 
of large data sets?


SELECT DISTINCT * FROM table ORDER BY field1,field2,field3,field4 DESC,field5;

to do the whole sorting and de-duplication in one step ... assuming you want to 
report duplicate entire rows only once ...


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users- 
>boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
>Sent: Tuesday, 26 September, 2017 11:37
>To: SQLite mailing list
>Subject: Re: [sqlite] Is there a way to perform a muti-level sort and 
>extract of large data sets?
>
>
>
>On 26 Sep 2017, at 6:24pm, Ron Barnes <rbar...@njdevils.net> wrote:
>
>> I need to sort them as follows...
>>
>> Sort Field 1 Ascending
>> Sort Field 2 Ascending WITHIN field 1 Sort Field 3 Ascending WITHIN 
>> field 2 WITHIN field 1 Sort Field 4 Descending WITHIN field 3 WITHIN 
>> field 2 WITHIN field
>1 <== This is a Date field and the most current (Highest) Date to float 
>up
>> Sort Field 5 Ascending WITHIN field 4 WITHIN field 3 WITHIN field 2
>WITHIN field 1
>
>Trivial in any SQL engine including SQLite.  It looks something like
>
>SELECT * FROM MyTable ORDER BY field1,field2,field3,field4
>DESC,field5
>
>SQL users do that sort of thing all the time.
>
>However, if you use just the above command, SQLite will have to perform 
>this sorting of 600M records each time you execute the command, which 
>could take quite a long time — minutes or hours depending on your 
>hardware.  So for any flavour of SQL you would probably tell it to 
>create an index …
>
>CREATE INDEX m_12345 ON MyTable (field1,field2,field3,field4
>DESC,field5)
>
>This tells SQL to perform the sorting and save the resulting order on 
>disk.  Then every time you perform the above SELECT command SQL notices 
>it already has the sort-order saved and just uses that one.
>This can change the amount of time taken to a few seconds.
>
>My one concern in reading your post is how your dates are formatted.
>When putting your date fields into your SQL table you will have to 
>ensure that dates are saved as a day number, or as text which naturally 
>sorts into date order, e.g. /DD/MM.  You should not expect SQL to 
>sort text such as "19 October 16" correctly.
>
>Simon.
>___
>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] Is there a way to perform a muti-level sort and extract of large data sets?

[Ron Barnes]

Thank you very much! 

I will research the two suggestions below.

As for your sort assumption, you are correct.

A  A  1  2
A  A  2  1
A  B  1  2
A  B  1  3
A  C  1   1

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Warren Young
Sent: Tuesday, September 26, 2017 2:14 PM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Is there a way to perform a muti-level sort and extract 
of large data sets?

On Sep 26, 2017, at 11:24 AM, Ron Barnes <rbar...@njdevils.net> wrote:
> 
> I have approximately 600 million records that need to be sorted

Where is the data now?

> There are 18 table entries.

You mean 18 columns per row, right?

> I also need to deduplicate the records based upon the sorted output file.

You speak of VB.NET, which means you don’t have a uniq tool as on POSIX systems:

   https://linux.die.net/man/1/uniq

If you can install Cygwin or WSL on these Windows boxes, then you’d have uniq, 
as well as a cross-platform solution.  SQLite is available for both Cygwin and 
WSL.

> I can take care of the deduplication (I think).

The basic functionality of uniq is indeed pretty simple: given sorted input, 
write as output only lines that don’t repeat the content of the previous input 
line.

The primary reason to mess with Cygwin or WSL on Windows is simply because 
using pre-built tools, you don’t have to debug and maintain it.  There’s value 
in “just run it through uniq.”  Even if you can write it in VB.net in half an 
hour, you’re vastly over-budget compared to the half second it takes me to type 
“ | uniq”.

> Sort Field 1 Ascending
> Sort Field 2 Ascending WITHIN field 1

I’m not sure what you mean by “WITHIN”.  Are you simply saying that you want 
the data sorted first by field 2 and then by field 1, so that when two records 
have the same field 1 content, that the output has that pair of records ordered 
by field 2?  E.g.

Field 1Field 2
-- 
A  B
A  C

As opposed to:

Field 1Field 2
-- 
A  C
A  B

If so, that’s trivial SQL, well-covered in Simon’s reply.
___
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] Is there a way to perform a muti-level sort and extract of large data sets?

[Ron Barnes]

Thank you so much - I will test this as soon as I get home!

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Tuesday, September 26, 2017 1:37 PM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Is there a way to perform a muti-level sort and extract 
of large data sets?



On 26 Sep 2017, at 6:24pm, Ron Barnes <rbar...@njdevils.net> wrote:

> I need to sort them as follows...
> 
> Sort Field 1 Ascending
> Sort Field 2 Ascending WITHIN field 1
> Sort Field 3 Ascending WITHIN field 2 WITHIN field 1 Sort Field 4 
> Descending WITHIN field 3 WITHIN field 2 WITHIN field 1 <== This is a 
> Date field and the most current (Highest) Date to float up Sort Field 
> 5 Ascending WITHIN field 4 WITHIN field 3 WITHIN field 2 WITHIN field 
> 1

Trivial in any SQL engine including SQLite.  It looks something like

SELECT * FROM MyTable ORDER BY field1,field2,field3,field4 DESC,field5

SQL users do that sort of thing all the time.

However, if you use just the above command, SQLite will have to perform this 
sorting of 600M records each time you execute the command, which could take 
quite a long time — minutes or hours depending on your hardware.  So for any 
flavour of SQL you would probably tell it to create an index …

CREATE INDEX m_12345 ON MyTable (field1,field2,field3,field4 DESC,field5)

This tells SQL to perform the sorting and save the resulting order on disk.  
Then every time you perform the above SELECT command SQL notices it already has 
the sort-order saved and just uses that one.  This can change the amount of 
time taken to a few seconds.

My one concern in reading your post is how your dates are formatted.  When 
putting your date fields into your SQL table you will have to ensure that dates 
are saved as a day number, or as text which naturally sorts into date order, 
e.g. /DD/MM.  You should not expect SQL to sort text such as "19 October 
16" correctly.

Simon.
___
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] Is there a way to perform a muti-level sort and extract of large data sets?

[Ron Barnes]

Hello All,

I have approximately 600 million records that need to be sorted and then 
extracted to a flat file.  I am unable to code a solution using visual Basic 
.NET.  It was suggested to me that a DB engine could perform my task for me.

Is there a way to accomplish this using the multi-level sort example below?

There are 18 table entries.  I need to extract all 18 entries to create 
individual records but in a certain order.  I also need to deduplicate the 
records based upon the sorted output file.  I can take care of the 
deduplication (I think).  I just need to get the records in the right order 
first.

I need to sort them as follows...

Sort Field 1 Ascending
Sort Field 2 Ascending WITHIN field 1
Sort Field 3 Ascending WITHIN field 2 WITHIN field 1
Sort Field 4 Descending WITHIN field 3 WITHIN field 2 WITHIN field 1 <== This 
is a Date field and the most current (Highest) Date to float up
Sort Field 5 Ascending WITHIN field 4 WITHIN field 3 WITHIN field 2 WITHIN 
field 1

Thank you in advance!

-Ron

___
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]

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!

SELECTcat, 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
  FROMVolume_Information) C) G
GROUP BY cat
ORDER BY cat

Works great BTW

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



STOP SPAMM ING ING ME  ASS FACE

From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of 
Jim Callahan <jim.callahan.orla...@gmail.com>
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 <rbar...@njdevils.net> 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 <sqlite-users@mailinglists.sqlite.org>
> 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; 
> 

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

[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

[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
   --  | -

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 <sqlite-users@mailinglists.sqlite.org>
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 <rbar...@njdevils.net> 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 <sqlite-users@mailinglists.sqlite.org>
> 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 <rbar...@njdevils.net> 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 

Re: [sqlite] Select Statement returning incorrect information

[Ron Barnes]

Hello Simon,

Got that one on my own!

The Data was created long before me but I do have the option to alter columns 
if needs be.

Hoping Richard can help out on the Date Select I'm struggling with!

Regards,
-Ron

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Tuesday, April 11, 2017 9:31 PM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Select Statement returning incorrect information


On 12 Apr 2017, at 2:27am, Ron Barnes <rbar...@njdevils.net> wrote:

> I needed to add the Cast parameter. 

Assuming you are actually storing integers, it might be better if you declared 
that column as integer in the first place.  Then you wouldn’t need the CAST.

However, well done for figuring it out, possibly with Richard’s help.

Simon.
___
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]

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 <sqlite-users@mailinglists.sqlite.org>
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 <rbar...@njdevils.net> 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
> 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] Select Statement returning incorrect information

[Ron Barnes]

I figured it out.
I needed to add the Cast parameter. 

SELECTcategory, COUNT(*) AS Expr1
FROM(SELECT(CASE 
WHEN CAST(VI_File_Len AS INTEGER) < 1024000 THEN 'Less Than 1MB' 
WHEN CAST(VI_File_Len AS INTEGER) < 2048000 THEN 'Less Than 2MB' 
WHEN CAST(VI_File_Len AS INTEGER) < 512 THEN 'Less Than 5MB' 
WHEN CAST(VI_File_Len AS INTEGER) < 1024 THEN 'Less Than 10MB' 
WHEN CAST(VI_File_Len AS INTEGER) < 10240 THEN 'Less Than 100MB' 
WHEN CAST(VI_File_Len AS INTEGER) < 51200 THEN 'Less Than 500MB' 
WHEN CAST(VI_File_Len AS INTEGER) < 102400 THEN 'Less Than 1GB'
WHEN CAST(VI_File_Len AS INTEGER) < 204800 THEN 'Less Than 2 GB' 
WHEN CAST(VI_File_Len AS INTEGER) < 512000 THEN 'Less Than 5 GB' 
WHEN CAST(VI_File_Len AS INTEGER) < 1024000 THEN 'Less Than 10 GB' 
WHEN CAST(VI_File_Len AS INTEGER) < 1536000 THEN 'Less Than 15 GB' 
WHEN CAST(VI_File_Len AS INTEGER) < 2048 THEN 'Less Than 20 GB' 
WHEN CAST(VI_File_Len AS INTEGER) < 3584 THEN 'Less Than 25 GB' 
ELSE 'Larger Than 25GB' END)  AS category
FROM   Volume_Information) derivedtbl_1
GROUP BY category



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Ron Barnes
Sent: Tuesday, April 11, 2017 9:15 PM
To: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org>
Subject: [sqlite] Select Statement returning incorrect information

Hello All,

With the select statement below and my test data of 43 files, I expected the 
following results

22 'Less than 1MB'
4 'Less than 5MB'
7 'Less than 10MB'
4 'Less than 15MB'
6 'Less than 20MB'

Instead I get 
16 'Less than 1MB'
18 'Less than 5MB'
9 'Larger than 25GB'

I have been pulling my hair out trying to figure out where I went south.  If 
someone could, would you point out my mistake, please?


SELECTcategory, COUNT(*) AS Expr1
FROM(SELECT(CASE 
WHEN VI_File_Len < 1024000 THEN 'Less Than 1MB' 
WHEN VI_File_Len < 2048000 THEN 'Less Than 2MB' 
WHEN VI_File_Len < 512 THEN 'Less Than 5MB' 
WHEN VI_File_Len < 1024 THEN 'Less Than 10MB' 
WHEN VI_File_Len < 10240 THEN 'Less Than 100MB' 
WHEN VI_File_Len < 51200 THEN 'Less Than 500MB' 
WHEN VI_File_Len < 102400 THEN 'Less Than 1GB'
WHEN VI_File_Len < 204800 THEN 'Less Than 2 GB' 
WHEN VI_File_Len < 512000 THEN 'Less Than 5 GB' 
WHEN VI_File_Len < 1024000 THEN 'Less Than 10 GB' 
WHEN VI_File_Len < 1536000 THEN 'Less Than 15 GB' 
WHEN VI_File_Len < 2048 THEN 'Less Than 20 GB' 
WHEN VI_File_Len < 3584 THEN 'Less Than 25 GB' 
ELSE 'Larger Than 25GB' END)  AS category
FROM   Volume_Information) derivedtbl_1
GROUP BY category


Regards,

-Ron

___
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] Select Statement returning incorrect information

[Ron Barnes]

Hello All,

With the select statement below and my test data of 43 files, I expected the 
following results

22 'Less than 1MB'
4 'Less than 5MB'
7 'Less than 10MB'
4 'Less than 15MB'
6 'Less than 20MB'

Instead I get 
16 'Less than 1MB'
18 'Less than 5MB'
9 'Larger than 25GB'

I have been pulling my hair out trying to figure out where I went south.  If 
someone could, would you point out my mistake, please?


SELECTcategory, COUNT(*) AS Expr1
FROM(SELECT(CASE 
WHEN VI_File_Len < 1024000 THEN 'Less Than 1MB' 
WHEN VI_File_Len < 2048000 THEN 'Less Than 2MB' 
WHEN VI_File_Len < 512 THEN 'Less Than 5MB' 
WHEN VI_File_Len < 1024 THEN 'Less Than 10MB' 
WHEN VI_File_Len < 10240 THEN 'Less Than 100MB' 
WHEN VI_File_Len < 51200 THEN 'Less Than 500MB' 
WHEN VI_File_Len < 102400 THEN 'Less Than 1GB'
WHEN VI_File_Len < 204800 THEN 'Less Than 2 GB' 
WHEN VI_File_Len < 512000 THEN 'Less Than 5 GB' 
WHEN VI_File_Len < 1024000 THEN 'Less Than 10 GB' 
WHEN VI_File_Len < 1536000 THEN 'Less Than 15 GB' 
WHEN VI_File_Len < 2048 THEN 'Less Than 20 GB' 
WHEN VI_File_Len < 3584 THEN 'Less Than 25 GB' 
ELSE 'Larger Than 25GB' END)  AS category
FROM   Volume_Information) derivedtbl_1
GROUP BY category


Regards,

-Ron

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

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

Re: [sqlite] Continuous recovery of journal

[Ron Barnes]

Hi All,

I am new to this mailing list.

Is anyone working with Visual Studio 2015 or belter and using reportviewer?  If 
yes, what was the procedure?
I'm having the devils of a time trying to generate reports using SQLite.

Would anyone be able to provide a TuT or maybe a link to one tha can help?

Regards,

-Ron

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

Re: [sqlite] Reporting Solutions that work with SQLite / VB 2015?

[Ron Barnes]

All,

Pardon me for interjecting.

Are there any reporting solutions that work with VB 2015?

-Ron

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of James K. Lowden
Sent: Saturday, April 1, 2017 8:11 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Reporting Solutions that work with SQLite / VB 2015?

On Sat, 25 Mar 2017 22:32:49 -0700
Cousin Stanley  wrote:

> James K. Lowden wrote:
> >   https://github.com/jklowden/sqlrpt
> 
>   I installed your  sqlrpt  program under debian linux
>   and managed to successfully run it with an sql query
>   from a file  
> 
> $ sqlrpt -d ../db/test.sql3 -q "`cat stocks_list.sql`" > 
> stocks.out
... 
> Can the floating point numbers be formatted
> for example  ah la  %8.4f

Cousin Stanley, 

Indeed you now can.  I added a -p option, documented in the man page.
You supply the column name & printf format string you want to use for it.  It 
works for numbers only: integers and floating point.  Strings continue to be 
formatted by tbl & troff. 

$ ./sqlrpt -p "Value,%'6.2f" -d db  -q "select Value  from T limit 4" | nroff 
-t | cat -s 
+---+
|Value  |
+---+
|  0.94 |
|  0.96 |
|  0.95 |
|  0.93 |
+---+


--jkl
___
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