Re: [sqlite] strftime and julianday

2017-09-27 Thread Simon Slavin
On 28 Sep 2017, at 4:12am, Stephen Chrzanowski wrote: > As a matter of fact, I started to work with doing my time deltas using > julianday, but, I ran into some float rounding issues and the math was > shorting me by at least a second, which made 1 hour look like 59

Re: [sqlite] strftime and julianday

2017-09-27 Thread Stephen Chrzanowski
I knew that over the course of human history, the calendar and time 'evaluations' were changed more than twice. What caught me off guard was that strftime was treated with the same ambiance to julianday as being +12hr from midnight when I thought I was asking for a midnight time value. This is

Re: [sqlite] strftime and julianday

2017-09-27 Thread Simon Slavin
On 28 Sep 2017, at 2:51am, Keith Medcalf wrote: >> I would have expected 0:00, not 12:00. Does this tie into julianday >> being a 12 hour offset? > > Six centuries or so ago it was "kind of difficult" to measure when the sun > was "directly underfoot" at the prime

Re: [sqlite] strftime and julianday

2017-09-27 Thread Keith Medcalf
That is of course ~6.5 thousand years or ~65 centuries. --- 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

Re: [sqlite] strftime and julianday

2017-09-27 Thread Keith Medcalf
>I would have expected 0:00, not 12:00. Does this tie into julianday >being a 12 hour offset? Six centuries or so ago it was "kind of difficult" to measure when the sun was "directly underfoot" at the prime meridian (0 deg, or Greenwich England, more or less) when your observation point was

Re: [sqlite] strftime and julianday

2017-09-27 Thread Stephen Chrzanowski
Perfect. Thanks guys. Thanks Igor for the proper offset as well. I'll file that for later reference. :] On Wed, Sep 27, 2017 at 8:13 PM, Igor Tandetnik wrote: > On 9/27/2017 8:06 PM, Stephen Chrzanowski wrote: > >> Messing around tonight with a work project, dealing with

Re: [sqlite] strftime and julianday

2017-09-27 Thread Igor Tandetnik
On 9/27/2017 8:06 PM, Stephen Chrzanowski wrote: Messing around tonight with a work project, dealing with times, I noticed the following: C:\Users\Stephen>sqlite3 SQLite version 3.8.10.1 2015-05-09 12:14:55 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open

Re: [sqlite] strftime and julianday

2017-09-27 Thread Richard Hipp
On 9/27/17, Stephen Chrzanowski wrote: > Messing around tonight with a work project, dealing with times, I noticed > the following: > > C:\Users\Stephen>sqlite3 > SQLite version 3.8.10.1 2015-05-09 12:14:55 > Enter ".help" for usage hints. > Connected to a transient in-memory

[sqlite] strftime and julianday

2017-09-27 Thread Stephen Chrzanowski
Messing around tonight with a work project, dealing with times, I noticed the following: C:\Users\Stephen>sqlite3 SQLite version 3.8.10.1 2015-05-09 12:14:55 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database.

Re: [sqlite] bug: failure to write journal reported as "disk I/O error"

2017-09-27 Thread Don V Nielsen
I'm sorry gentlemen, but the argument has gotten thick and petulant. Every complaint and response is resolving down to a mainframe line of thought (thank God), which few today are willing to accept. That is, the SQLite software is kept compatible with its root. How many System 370 Cobol programs

Re: [sqlite] bug: failure to write journal reported as "disk I/O error"

2017-09-27 Thread Guy Harris
On Sep 27, 2017, at 10:00 AM, Keith Medcalf wrote: > On Wednesday, 27 September, 2017 10:39, Guy Harris wrote: > >> On Sep 27, 2017, at 6:58 AM, Keith Medcalf wrote: > >>> Well, the terminology is correct. These *ARE* I/O Errors.

Re: [sqlite] bug: failure to write journal reported as "disk I/O error"

2017-09-27 Thread Keith Medcalf
On Wednesday, 27 September, 2017 10:39, Guy Harris wrote: >On Sep 27, 2017, at 6:58 AM, Keith Medcalf wrote: >> Well, the terminology is correct. These *ARE* I/O Errors. The >> system attempted I/O. It failed. Hence the term I/O Error. > Just don't

Re: [sqlite] Recurring Dates

2017-09-27 Thread Stephen Chrzanowski
Check that... 'Week[s]' isn't supported. I tested at the command line, works very well, thanks for the effort Dave. Appreciated. Now to understand how EXACTLY this works. On Wed, Sep 27, 2017 at 12:30 PM, Stephen Chrzanowski wrote: > . that indicates Daily, Weekly,

Re: [sqlite] bug: failure to write journal reported as "disk I/O error"

2017-09-27 Thread Guy Harris
On Sep 27, 2017, at 6:58 AM, Keith Medcalf wrote: > Well, the terminology is correct. These *ARE* I/O Errors. The system > attempted I/O. It failed. Hence the term I/O Error. Just don't call it a "disk I/O error". > It is irrelevant whether the error was caused

Re: [sqlite] Recurring Dates

2017-09-27 Thread Stephen Chrzanowski
From my original; *Take for instance - using today as the base date, a date of Aug 1, 2016 with a yearly recurring date, I'd like to get Aug 1, 2018.- using today as the base date, a date of Aug 1, 2016 with a weekly recurring date, I'd like to get Sept 2, 2017.* Today being Sept 27, 2017. Aug

Re: [sqlite] ADO.NET driver upgrade puzzle

2017-09-27 Thread Barry Smith
Quite strange that Prepare() makes a difference. The system.data.sqlite documentation states that Prepare() does nothing, and a code inspection of the system.data.sqlite source shows that it does nothing but check that the command hasn't been disposed, and that the connection is still valid

Re: [sqlite] Recurring Dates

2017-09-27 Thread David Raymond
Or pared down slightly... with recursive dateGuesses (dateGuess) as ( values ('original-date-here') union all select date(dateGuess, '+1 years' / '+1 months' / '+7 days' / '+1 days') from dateGuesses where dateGuess < date('now')) select max(dateGuess) as nextDate from dateGuesses; with

Re: [sqlite] All of a sudden I must commit after every single change and I don't know what happened.

2017-09-27 Thread Keith Medcalf
On Wednesday, 27 September, 2017 00:35, bensonbear wrote: >I mentioned I was using python and its sqlite3 module, but did >not specify I was using it in simplest possible default mode, just >opening the connection with no arguments. >I didn't need to know more before --

Re: [sqlite] Recurring Dates

2017-09-27 Thread Donald Griggs
Hello Stephen, Are the nice date/time features at https://sqlite.org/lang_datefunc.html enough? For example: select date ('2016-08-01', '+1 year'); -- gives 2017-08-01 Regarding: "- using today as the base date, a date of Aug 1, 2016 with a weekly recurring date, I'd like to get Sept 2,

Re: [sqlite] All of a sudden I must commit after every single change and I don't know what happened.

2017-09-27 Thread Simon Slavin
On 27 Sep 2017, at 7:35am, bensonbear wrote: > I could not think of anything very useful to try, but in any case I > found pretty quickly that the problem was the fact that activity > elsewhere had nearly filled up my root partition. I don't know > exactly how this

Re: [sqlite] Recurring Dates

2017-09-27 Thread David Raymond
For a procedure anyway, something like... with recursive iterations (iteration, dateGuess) as ( values (1, '2016-08-01') union all select iteration + 1, date(dateGuess, '1 years') from iterations where dateGuess < date('now')) select dateGuess from iterations order by iteration desc limit 1;

Re: [sqlite] When is db size an issue?

2017-09-27 Thread Simon Slavin
On 27 Sep 2017, at 1:41pm, Jason T. Slack-Moehrle wrote: > However, does anyone have any thoughts about the size? Should I be > concerned? Is there a theoretical limit I should keep in the back of my > mind? Not, theoretical, practical:

[sqlite] Recurring Dates

2017-09-27 Thread Stephen Chrzanowski
Does anyone have any decent functions or procedures that when given a date and a recurring type that SQLite gives the current or next date? Take for instance - using today as the base date, a date of Aug 1, 2016 with a yearly recurring date, I'd like to get Aug 1, 2018. - using today as the base

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

2017-09-27 Thread Donald Griggs
Hi Ron, It may be that increasing the sqlite cache size will substantially reduce the time for either the CREATE INDEX or the SELECT ... ORDER BY instructions (depending upon which method you choose). https://sqlite.org/pragma.html#pragma_cache_size On Wed, Sep 27, 2017 at 1:58 AM, jungle

Re: [sqlite] All of a sudden I must commit after every single change and I don't know what happened.

2017-09-27 Thread R Smith
On 2017/09/27 8:35 AM, bensonbear wrote: I didn't even know that outside transactions, SQLite by default itself commits automatically after each statement, which I would not have wanted. However, the python module by default implicitly inserts "begin"s so that statements are grouped into a

Re: [sqlite] CREATE FOREIGN KEY support

2017-09-27 Thread David Raymond
But then you don't have to worry about issues with foreign keys or triggers, or the time to copy data, etc. But yes it does "void your warranty" to do so. pragma writable_schema = true; begin; --important update sqlite_master set sql = '..., foreign key () references blah ()...' WHERE type =

Re: [sqlite] bug: failure to write journal reported as "disk I/O error"

2017-09-27 Thread Keith Medcalf
Well, the terminology is correct. These *ARE* I/O Errors. The system attempted I/O. It failed. Hence the term I/O Error. It is irrelevant whether the error was caused because the heads on the tape drive need cleaning, access was denied to spool storage, the disk was full, someone yanked

Re: [sqlite] When is db size an issue?

2017-09-27 Thread David Raymond
With the way vacuum works you can temporarily be using the equivalent space of 2 full extra copies. Also, one of those copies goes into your temp folder, so if the size exceeds what's available for your temp folder you won't be able to vacuum. Unless of course you either change your temp folder

Re: [sqlite] CREATE FOREIGN KEY support

2017-09-27 Thread Igor Tandetnik
On 9/27/2017 9:18 AM, Igor Korot wrote: On Wed, Sep 27, 2017 at 7:04 AM, Clemens Ladisch wrote: Alternatively, if you're feeling adventurous, you can use PRAGMA writable_schema to modify the table definition in place: https://stackoverflow.com/a/42970982/11654 Just

Re: [sqlite] CREATE FOREIGN KEY support

2017-09-27 Thread Igor Korot
Hi, Clemens, On Wed, Sep 27, 2017 at 7:04 AM, Clemens Ladisch wrote: > Igor Korot wrote: >> 3. DROP TABLE ; >> >> On step 3 all ttriggers and indexes will be dropped as well, right? > > Yes. > >> 4. CREATE TABLE (, FOREIGN KEY() REFERENCE pkTable() ) AS SELECT * >> FROM

Re: [sqlite] When is db size an issue?

2017-09-27 Thread Richard Hipp
On 9/26/17, Jason T. Slack-Moehrle wrote: > Hello All, > > Off and on for the last few years I have been writing an e-mail client to > scratch a personal itch. I store the mail in SQLite and attachments on the > file system. However, I recently brought in all of my mail

[sqlite] When is db size an issue?

2017-09-27 Thread Jason T. Slack-Moehrle
Hello All, Off and on for the last few years I have been writing an e-mail client to scratch a personal itch. I store the mail in SQLite and attachments on the file system. However, I recently brought in all of my mail for the last 15 years from mbox format. Now, my database size is over 10gb.

Re: [sqlite] All of a sudden I must commit after every single change and I don't know what happened.

2017-09-27 Thread bensonbear
D.Richard Hipp writes: | Good question. I suspect that whatever is going wrong has little or | nothing to do with SQLite, though. What other debugging steps have | you taken? I could not think of anything very useful to try, but in any case I found pretty quickly that the problem was the

[sqlite] When is db size an issue?

2017-09-27 Thread Jason T. Slack-Moehrle
Hello All, Off and on for the last few years I have been writing an e-mail client to scratch a personal itch. I store the mail in SQLite and attachments on the file system. However, I recently brought in all of my mail for the last 15 years from mbox format. Now, my database size is over 10gb.

Re: [sqlite] Why is Sqlite mediatype not registered at iana

2017-09-27 Thread Paul van Genuchten
Hi Clemens, thank you for your reply. I’d be happy to volunteer for that exercise, however not sure if i’m a good fit, since i’m currently not a member of the sqlite consortium. Related to your comment to register sqlite as a structured syntax suffix (RFC 6839). I’d say why not both. For my

Re: [sqlite] ADO.NET driver upgrade puzzle

2017-09-27 Thread Roberts, Barry (FINTL)
Hi, I am aware of the sqlite FAQ, and especially the comments regarding file systems. We only ever use NTFS file system, and never have any FAT or FAT32 file systems. Given that the old 1.0.80.0 driver is totally stable, and the newer one is not, on the same exact hardware, I do not think the

Re: [sqlite] CREATE FOREIGN KEY support

2017-09-27 Thread Clemens Ladisch
Igor Korot wrote: > 3. DROP TABLE ; > > On step 3 all ttriggers and indexes will be dropped as well, right? Yes. > 4. CREATE TABLE (, FOREIGN KEY() REFERENCE pkTable() ) AS SELECT * > FROM temp; > > what would be the best way to read [triggers and indexes] and apply to the > step 4? Run