Re: [sqlite] SQL help: by-range aggregation

On Wed, Mar 8, 2017 at 3:47 PM, Clemens Ladisch wrote: > Dominique Devienne wrote: > > On Wed, Mar 8, 2017 at 3:16 PM, Hick Gunter wrote: > >> Alternatively create a "range" table, insert your defined ranges and > >> join/subquery to the original query. > >

Re: [sqlite] SQL help: by-range aggregation

On Wed, Mar 8, 2017 at 3:16 PM, Hick Gunter wrote: > Alternatively create a "range" table, insert your defined ranges and > join/subquery to the original query. > > Create table range (label text, from integer, to integer); > That's the easy part (and a CTE can probably

Re: [sqlite] SQL help: by-range aggregation

On 8 Mar 2017, at 2:33pm, Dominique Devienne wrote: > Same constraints of the "thousand separator" one, this will be a view in a > DB to be viewed by any SQLite client, so not allowed here. --DD In that case, I think Hick’s solution is good. Do it in two steps: construct

Re: [sqlite] Query default lookaside pool size

On Wed, Mar 8, 2017 at 2:41 PM, Simon Slavin wrote: > > On 8 Mar 2017, at 11:09am, Kim Gräsman wrote: > >> Is there a way to query SQLite build parameters at runtime, more >> specifically SQLITE_DEFAULT_LOOKASIDE? > >

Re: [sqlite] sqlite3 feature or regression

On 8 Mar 2017, at 2:16pm, don v nielsen wrote: > Vermes, I'm late to the party but would still like to comment. The problem is > the ruby code, not sqlite. The following is what you coded in the Ruby: > >db.execute("select szamla,megnevezes from proba") do |row| >

Re: [sqlite] SQL help: by-range aggregation

Dominique Devienne wrote: > On Wed, Mar 8, 2017 at 3:16 PM, Hick Gunter wrote: >> Alternatively create a "range" table, insert your defined ranges and >> join/subquery to the original query. > > That's the easy part [...] > but the join/subquery you mention is still leaving me

Re: [sqlite] Why isn't my time formatting working?

On Wednesday, 8 March, 2017 13:40, Paul Sanderson wrote: > The vast majority of dates I see in SQLite databases are unix epoch > integer times (seconds since 1/1/1980) with unix milli seconds a > close second. > Efficient to store, sort and do date arithmetic on

Re: [sqlite] Why isn't my time formatting working?

Ooops. .NET Epoch is UTC based. OLE Time is localtime based. > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Keith Medcalf > Sent: Wednesday, 8 March, 2017 18:32 > To: SQLite mailing list > Subject: Re: [sqlite] Why isn't

Re: [sqlite] SQL help: by-range aggregation

On 8 Mar 2017, at 3:35pm, Dominique Devienne wrote: > > Then the results where in alphabetical order of bucket names, > so I had to re-join on ranges to order by ranges.low. You should be able to just add the ORDER BY clause to the original JOIN: SELECT ranges.name,

Re: [sqlite] SQL help: by-range aggregation

On 2017/03/08 5:35 PM, Dominique Devienne wrote: Now I only need a CTE to dynamically generate the ranges, Well, that seems like fun! If I may... -- CTEs -- Units Table (B, KB, MB, etc.) WITH UT(id, uStr, byteDiv) AS ( SELECT 0, 'B', 1 UNION ALL SELECT UT.id+1,

Re: [sqlite] Why isn't my time formatting working?

On 3/8/17, Rob Richardson wrote: > Hello! > > I have a table with times stored as strings. I massaged them into a form > that strftime() should be able to work with, but it's not working. Here's a > little query using the string as it is currently formatted: > > select

Re: [sqlite] Why isn't my time formatting working?

Hi Rob, The format string of '%d/%m/%Y %H:%M:%S' describes what you want as output, not what you're supplying as input. You can use substr() and concatenation || to mash up your original string into the ISO format (which is much easier to handle anyway.)

Re: [sqlite] Why isn't my time formatting working?

> On Mar 8, 2017, at 11:59 AM, Rob Richardson wrote: > > Given the lack of an indication of the return type, it seemed to me to be > reasonable to assume that since I'm passing in a string as one of the > arguments, I'd get a datetime object out. SQLite doesn’t have

Re: [sqlite] Why isn't my time formatting working?

Correct. The ISO strings are the de-facto standard since that's what all the date and time functions take in. http://www.sqlite.org/lang_datefunc.html "The strftime() routine returns the date formatted according to the format string specified as the first argument." It's there so you can store

[sqlite] Why isn't my time formatting working?

Hello! I have a table with times stored as strings. I massaged them into a form that strftime() should be able to work with, but it's not working. Here's a little query using the string as it is currently formatted: select strftime('%d/%m/%Y %H:%M:%S', '03/07/2017 13:06:03') This query

Re: [sqlite] Why isn't my time formatting working?

strtime is for formatting a standard format into whatever format you give it. You're thinking of strtime which would parse a given string based on your format. SQLite includes the format function, but not the parse one. -Original Message- From: sqlite-users

Re: [sqlite] Why isn't my time formatting working?

But the strftime() function is supposed to work with whatever format I give it, isn't it? According to the documentation, %d is a two-digit day, %m is a two-digit month, and so on. Is there truly no way to convert my original string into a datetime object? RobR -Original Message-

Re: [sqlite] Why isn't my time formatting working?

On 8 Mar 2017, at 7:33pm, Rob Richardson wrote: > But the strftime() function is supposed to work with whatever format I give > it, isn't it? No. How could that work for a date like 3/4/2017 ? It wouldn’t know if that was the 3rd of April or the 4th of March. >

Re: [sqlite] Why isn't my time formatting working?

Thank you. The https://www.sqlite.org/lang_datefunc.html page doesn't seem to make it clear what strftime() returns. The specification it gives for strftime() is: strftime(format, timestring, modifier, modifier, ...) Given the lack of an indication of the return type, it seemed to me to be

Re: [sqlite] Why isn't my time formatting working?

Rob Richardson wrote... But the strftime() function is supposed to work with whatever format I give it, isn't it? According to the documentation, %d is a two-digit day, %m is a two-digit month, and so on. Is there truly no way to convert my original string into a datetime object? I

Re: [sqlite] Why isn't my time formatting working?

The vast majority of dates I see in SQLite databases are unix epoch integer times (seconds since 1/1/1980) with unix milli seconds a close second. Efficient to store, sort and do date arithmetic on but need to be converted to display. I also see unix nano seconds, 100 nano seconds, windows

Re: [sqlite] Why isn't my time formatting working?

On 2017/03/08 9:59 PM, Rob Richardson wrote: Thank you. The https://www.sqlite.org/lang_datefunc.html page doesn't seem to make it clear what strftime() returns. The specification it gives for strftime() is: strftime(format, timestring, modifier, modifier, ...) It returns a string, they

Re: [sqlite] Why isn't my time formatting working?

On 2017/03/08 10:40 PM, Paul Sanderson wrote: The vast majority of dates I see in SQLite databases are unix epoch integer times (seconds since 1/1/1980) with unix milli seconds a close second. Efficient to store, sort and do date arithmetic on but need to be converted to display. I also see

[sqlite] Query default lookaside pool size

Hi all, Is there a way to query SQLite build parameters at runtime, more specifically SQLITE_DEFAULT_LOOKASIDE? Thanks, - Kim ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] SQL help: by-range aggregation

On Wed, Mar 8, 2017 at 2:53 PM, Simon Slavin wrote: > On 8 Mar 2017, at 1:46pm, Dominique Devienne wrote: > > I'd like to aggregate the sum of the size per-parent, from 1KB, to each > > range > > from low to 8*low (i.e. [low, 8*low)), but w/o having to

Re: [sqlite] Query default lookaside pool size

On 8 Mar 2017, at 11:09am, Kim Gräsman wrote: > Is there a way to query SQLite build parameters at runtime, more > specifically SQLITE_DEFAULT_LOOKASIDE? Simon. ___

Re: [sqlite] Why isn't my time formatting working?

On 08 Mar 2017 at 20:40, Paul Sanderson wrote: > The vast majority of dates I see in SQLite databases are unix epoch integer > times (seconds since 1/1/1980) with unix milli seconds a close second. > Efficient to store, sort and do date arithmetic on but need to be

Re: [sqlite] Why isn't my time formatting working?

Oops - typo in my post above. Unix is of course secs since 1970. and Tim yes I too always use numerical dates - each to their own though - my post wasn't trying to say what is best, just what I see. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786

Re: [sqlite] Error using multiline command line argument with dot-command

Hello, On 2017-03-07 18:16, Dan Kennedy wrote: On 03/08/2017 12:03 AM, Rob Golsteijn wrote: I want to report a minor issue for the Sqlite shell. It does not handle multiline command line arguments in which the second line contains a dot-command correctly. If the same statements are passed

[sqlite] SQL help: by-range aggregation

Given a table with a (parent, child) PK, which carries a size "data" column, I'd like to aggregate the sum of the size per-parent, from 1KB, to each range from low to 8*low (i.e. [low, 8*low)), but w/o having to write my hand all possible combination, with extra points for formatting the range

Re: [sqlite] Can sqlite3_blob_write be done through SQL?

On 03/05/2017 10:19, Clemens Ladisch wrote: or change the row IDs so that they are unique in the entire database. Thanks for your suggestion. I followed it, and made rowids unique in the table b with a blob field. But I've ran into another problem: commit of the subsequent unrelated

Re: [sqlite] Can sqlite3_blob_write be done through SQL?

On 8 Mar 2017, at 10:10pm, Yuri wrote: > This fails: > > sqlite3_blob_open // table b with rowids unique in db > > sqlite3_blob_write // table b > > BEGIN > > UPDATE // table a > > UPDATE // table a > > COMMIT // FAILS SQLITE_BUSY! That would appear to be the bug we

Re: [sqlite] SQL help: by-range aggregation

On 8 Mar 2017, at 1:46pm, Dominique Devienne wrote: > I'd like to aggregate the sum of the size per-parent, from 1KB, to each > range > from low to 8*low (i.e. [low, 8*low)), but w/o having to write my hand all > possible > combination, with extra points for formatting the

Re: [sqlite] SQL help: by-range aggregation

Alternatively create a "range" table, insert your defined ranges and join/subquery to the original query. Create table range (label text, from integer, to integer); -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Simon

Re: [sqlite] sqlite3 feature or regression

Vermes, I'm late to the party but would still like to comment. The problem is the ruby code, not sqlite. The following is what you coded in the Ruby: db.execute("select szamla,megnevezes from proba") do |row| In some shape or fashion, the result set is getting mangled by the update. When

Re: [sqlite] SQLite Recursive Common Table Expression suggestion

On Wed, Mar 8, 2017 at 3:47 AM, petern wrote: > Things have changed somewhat since I wrote those comments. After the > introduction of row values in 3.15 https://www.sqlite.org/rowvalue.html , > at least, now the sqlite ecosystem can cope with efficient vector

Re: [sqlite] Is this safe use of SELECT in an INSERT?

Thanks for that. For now, any deletions would be "en masse" so gaps wouldn't be a problem, but the max() idea might be useful later. Sent from my Samsung Galaxy S7 - powered by Three Original message From: Clemens Ladisch Date: 08/03/2017 08:59

Re: [sqlite] sqlite3 feature or regression

On 2017/03/07 5:00 PM, Vermes Mátyás wrote: On Mon, 6 Mar 2017 18:34:40 -0500 Richard Hipp wrote: I am not a Ruby programmer either nor a real SQLite user. I am interested in writing SQL interfaces to href="http://github.com/mrev11/ccc3;>CCC to various databases. Ruby was

Re: [sqlite] Is this safe use of SELECT in an INSERT?

Graham Holden wrote: > insert or ignore into Servers values ('MyServer',12345,( select > count (*) from Servers ) ) > > is the "count (*)" guaranteed to be the count before the insert? Yes. But if any row was ever deleted, that value might alread by used. You could make serverIdx

Re: [sqlite] sqlite3 feature or regression

On 3/7/17, Vermes Mátyás wrote: >> It is also unnecessarily complex and slow. > > The script ... is written in Ruby so that > everybody can run it, and see its _results_. The point is that I cannot run your Ruby script on my Ubuntu desktop because I get an error: