Re: [sqlite] MONTH function

[Gabor Grothendieck]

Or even:

"select cast(strftime('%m') as integer)=6"

On Sun, Jun 23, 2013 at 4:16 PM, Gabor Grothendieck
 wrote:
> Which can also be written as:
>
> "select cast(strftime('%m','now') as integer)=6"
>
> On Sun, Jun 23, 2013 at 4:11 PM, Patrik Nilsson
>  wrote:
>> You can write:
>>
>> "select cast(strftime('%m',datetime('now')) as integer)=6"
>> 1
>>
>>
>> On 06/23/2013 09:45 PM, Lucas wrote:
>>> Hello,
>>>
>>> I am testing SQLIte as we are considering to change our DB Server but I
>>> found that a feature is missing, the function Month:
>>>
>>> SELECT SUM( NINGRESO ) AS ndev FROM APUNTES WHERE MONTH(FECHA) = 1
>>>
>>> This works perfect under MySQL or MSSQL.
>>>
>>> Do you plan to introduce MONTH and YEAR functions?.
>>>
>>>
>>> Also, the function strftime('%m', fecha) does not solve the issue:
>>>
>>> SELECT SUM( NINGRESO ) AS ndev FROM APUNTES WHERE strftime('%m', fecha) = 1
>>>
>>>
>>> Please, any idea of how to solve it.
>>>
>>>
>>> Thank you. Best regards,
>>>
>>> Lucas de Beltran
>>> Caritas España
>>>
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>> --
>> ASCII ribbon campaign ( )
>>  against HTML e-mail   X
>>  www.asciiribbon.org  / \
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> --
> Statistics & Software Consulting
> GKX Group, GKX Associates Inc.
> tel: 1-877-GKX-GROUP
> email: ggrothendieck at gmail.com



--
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] MONTH function

[Gabor Grothendieck]

Which can also be written as:

"select cast(strftime('%m','now') as integer)=6"

On Sun, Jun 23, 2013 at 4:11 PM, Patrik Nilsson
 wrote:
> You can write:
>
> "select cast(strftime('%m',datetime('now')) as integer)=6"
> 1
>
>
> On 06/23/2013 09:45 PM, Lucas wrote:
>> Hello,
>>
>> I am testing SQLIte as we are considering to change our DB Server but I
>> found that a feature is missing, the function Month:
>>
>> SELECT SUM( NINGRESO ) AS ndev FROM APUNTES WHERE MONTH(FECHA) = 1
>>
>> This works perfect under MySQL or MSSQL.
>>
>> Do you plan to introduce MONTH and YEAR functions?.
>>
>>
>> Also, the function strftime('%m', fecha) does not solve the issue:
>>
>> SELECT SUM( NINGRESO ) AS ndev FROM APUNTES WHERE strftime('%m', fecha) = 1
>>
>>
>> Please, any idea of how to solve it.
>>
>>
>> Thank you. Best regards,
>>
>> Lucas de Beltran
>> Caritas España
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> --
> ASCII ribbon campaign ( )
>  against HTML e-mail   X
>  www.asciiribbon.org  / \
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



--
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] MONTH function

[Patrik Nilsson]

You can write:

"select cast(strftime('%m',datetime('now')) as integer)=6"
1


On 06/23/2013 09:45 PM, Lucas wrote:
> Hello,
> 
> I am testing SQLIte as we are considering to change our DB Server but I
> found that a feature is missing, the function Month:
> 
> SELECT SUM( NINGRESO ) AS ndev FROM APUNTES WHERE MONTH(FECHA) = 1
> 
> This works perfect under MySQL or MSSQL.
> 
> Do you plan to introduce MONTH and YEAR functions?.
> 
> 
> Also, the function strftime('%m', fecha) does not solve the issue:
> 
> SELECT SUM( NINGRESO ) AS ndev FROM APUNTES WHERE strftime('%m', fecha) = 1
> 
> 
> Please, any idea of how to solve it.
> 
> 
> Thank you. Best regards,
> 
> Lucas de Beltran
> Caritas España
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 

-- 
ASCII ribbon campaign ( )
 against HTML e-mail   X
 www.asciiribbon.org  / \
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] MONTH function

[Patrik Nilsson]

"select strftime('%m',datetime('now'))='06'"
1

"select strftime('%m',datetime('now'))=6"
0

It is considered as a string, not a number.


On 06/23/2013 09:45 PM, Lucas wrote:
> Hello,
> 
> I am testing SQLIte as we are considering to change our DB Server but I
> found that a feature is missing, the function Month:
> 
> SELECT SUM( NINGRESO ) AS ndev FROM APUNTES WHERE MONTH(FECHA) = 1
> 
> This works perfect under MySQL or MSSQL.
> 
> Do you plan to introduce MONTH and YEAR functions?.
> 
> 
> Also, the function strftime('%m', fecha) does not solve the issue:
> 
> SELECT SUM( NINGRESO ) AS ndev FROM APUNTES WHERE strftime('%m', fecha) = 1
> 
> 
> Please, any idea of how to solve it.
> 
> 
> Thank you. Best regards,
> 
> Lucas de Beltran
> Caritas España
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 

-- 
ASCII ribbon campaign ( )
 against HTML e-mail   X
 www.asciiribbon.org  / \
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VACUUM and PRAGMA temp_store

[Richard Hipp]

On Sun, Jun 23, 2013 at 5:06 AM, Mario M. Westphal  wrote:

>
> When creating/opening a file in Windows, an application can mark this file
> as "temporary" (flag: FILE_ATTRIBUTE_TEMPORARY) and Windows will try to
> keep
> it in memory if sufficient memory is available, avoiding all writes to the
> medium. Does SQLite use this feature on Windows when it creates temporary
> files?
>

Yes.  It sets FILE_ATTRIBUTE_TEMPORARY, FILE_ATTRIBUTE_HIDDEN, and
FILE_ATTRIBUTE_DELETE_ON_CLOSE.

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

Re: [sqlite] MONTH function

[Simon Slavin]

On 23 Jun 2013, at 8:45pm, Lucas  wrote:

> Also, the function strftime('%m', fecha) does not solve the issue:
> 
> SELECT SUM( NINGRESO ) AS ndev FROM APUNTES WHERE strftime('%m', fecha) = 1

We want to know what format you are storing your dates in.  Can you do this:

SELECT fecha FROM apuntes LIMIT 3

and tell us the results ?

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

[sqlite] MONTH function

[Lucas]

Hello,

I am testing SQLIte as we are considering to change our DB Server but I 
found that a feature is missing, the function Month:


SELECT SUM( NINGRESO ) AS ndev FROM APUNTES WHERE MONTH(FECHA) = 1

This works perfect under MySQL or MSSQL.

Do you plan to introduce MONTH and YEAR functions?.


Also, the function strftime('%m', fecha) does not solve the issue:

SELECT SUM( NINGRESO ) AS ndev FROM APUNTES WHERE strftime('%m', fecha) = 1


Please, any idea of how to solve it.


Thank you. Best regards,

Lucas de Beltran
Caritas España


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

[sqlite] Multiple connections in same process out of sync

[Yuriy Stelmakh]

Hi all, I'm having an issue when using multiple connections inside
same process. Shared -mode is off in my case because b-tree locking
slowed down performance when shared cache was on. The issue i'm having
is that when one connection performs an insert, sometimes the other
connection's page cache does not get flushed and the new rows do not
come back. The other connection doesn't seem to be aware of the
inserted row.
Has anyone seen this issue? have any suggestions?

Thanks,

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

[sqlite] Converting my sqlite database to utf-8

[user]

Hello,

I want to link my database to android and I am writing arabic words in it so
I need to convert these words to utf-8 or else I won't be able to select
them from android I am gonna get question marks so how to do that if I have
already written the data but it's written in ansi how to transform it and if
I need to create a new one how can I write my data in utf-8?

thanks.



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Converting-my-sqlite-database-to-utf-8-tp69578.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to interrupt a long running update without roll back?

[RSmith]

Bit long ago to recall exactly, but to be honest I think it was quite more sinister even, if memory serves, I was modifying the 
rowids too. Either way I avoid messing with rowids or relying on them altogether now as a short-cut to updating - but it s quite 
safe to do so.


As an aside, while trying to remember my predicament and reading the link Donald sent, it prompted me to simply compute what a 
logger would have to do to exhaust the possible rowid count as it stands now as an assessment of whether it is safe to assume there 
will be enough rowids to last a good amount of time.


Basically worked out that if you wrote (assuming your hardware was capable) a thousand records every one-thousandth of a second (or 
simply a million records a second), you would have 86,400,000,000 (~86 Billion) records per day and 365.224 days per year, that's 
31,555,353,600,000 (~31 Trillion) records per year.
Now dividing the max_rowid by it (9223372036854775807 / 3155535360) yields ~292,291 years.  That's almost three-hundred-thousand 
years.
An entire human lifespan is less than a 100th of a percent of that amount of time, or put in a less mathematical way, it is several 
times longer than the entire recorded human history up to now.


I think it's safe to assume that your hardware will die before the rowids do. :)
Cheers!


On 2013/06/23 14:33, Donald Griggs wrote:

Regarding:  ...then went horribly wrong at some point after rowids consumed all 
of 32 bits...

If your application treated ROWIDs as 32-bit integers, that may possibly have been the cause of your problem, since  ROWID's are 
64-bit integers.


The maximum ROWID is 9223372036854775807 http://www.sqlite.org/autoinc.html
and would seem hard to exhaust with your application in our lifetimes.






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

Re: [sqlite] unique id for table

[e-mail mgbg25171]

Yes thanks for the advice...I've already combined the "raw" data i.e.
vectors straight out of the annual reports and the calculated vectors e.g..
"noplat" derived from it which I've given a type "calcd" in the type field
of the same table "itms".
There is some market wide data however, which might not fit into this
vector table Even though the individual items are stored as individual
records.I'm just at the stage of integrating SQLITE with forth and
everything's up for grabs at the moment.



On 23 June 2013 12:42, Simon Slavin  wrote:

>
> On 23 Jun 2013, at 12:26pm, e-mail mgbg25171 
> wrote:
>
> > The "table as a number" idea is necessary to fit my model
> > where word definitions are stored as streams of code pointers (NUMBERS)
> > that just get called.
> > i.e. EVERYTHING has to be a number
>
> That makes perfect sense.
>
> If these tables all have the same fields in you might want to refactor
> your SQLite schema so that these tables are actually all one big table.
>  The thing you currently consider to be a table name would just be a field
> in the big table.
>
> This would give you a single rowid which would indicate both the table and
> the (currently) row in that table.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to interrupt a long running update without roll back?

[Donald Griggs]

Regarding:   ...then went horribly wrong at some point after rowids
consumed all of 32 bits...

If your application treated ROWIDs as 32-bit integers, that may possibly
have been the cause of your problem, since  ROWID's are 64-bit integers.

The maximum ROWID is  9223372036854775807
http://www.sqlite.org/autoinc.html
and would seem hard to exhaust with your application in our lifetimes.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unique id for table

[Simon Slavin]

On 23 Jun 2013, at 12:26pm, e-mail mgbg25171  wrote:

> The "table as a number" idea is necessary to fit my model
> where word definitions are stored as streams of code pointers (NUMBERS)
> that just get called.
> i.e. EVERYTHING has to be a number

That makes perfect sense.

If these tables all have the same fields in you might want to refactor your 
SQLite schema so that these tables are actually all one big table.  The thing 
you currently consider to be a table name would just be a field in the big 
table.

This would give you a single rowid which would indicate both the table and the 
(currently) row in that table.

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

Re: [sqlite] unique id for table

[e-mail mgbg25171]

I'm writing a variation of forth (It's got an IDE and 3-level stepping
debugger already)
that's actually a company valuation programming language (Can't get on with
spreadsheets hiding everything)
It accomodates words that are financial report line items (vectors) and
their manipulation
and I'm using SQLITE to store such vectors scraped from pdfs
at various stages i.e. until they fit a standard form allowing comparison
between companies.
The "table as a number" idea is necessary to fit my model
where word definitions are stored as streams of code pointers (NUMBERS)
that just get called.
i.e. EVERYTHING has to be a number
I'm therefore proposing to get at line items and any other piece of
financial info using a number for the table and one for the first row-id
from which you can get the name and type to search for the other values in
the vector.
That's about it and thanks for your interest!







On 23 June 2013 10:06, RSmith  wrote:

> There is of course no straight-forward way to do this, as I've noted Simon
> said already. But my curiosity is very piqued - Mind sharing what kind of
> application requires number-only calling? You using this on a calculator of
> sorts? - If so (or otherwise), some details would be interesting to know.
> (I do some embedded systems, never tried SQLite on it but might, hence the
> interest).
>
> I reply On-list as others might be interested, but will take the
> discussion off-list as it may not really be in the scope of the list (I
> think) if your awaited reply prompts further discussion.
>
> Have a great day!
>
> On 2013/06/22 20:27, e-mail mgbg25171 wrote:
>
>> I need to access a row just using numbers.
>> The row id's fine but can I specify the table using a numeric id too?
>> If not I'll just create a look-up table so there's no problem.
>> I'm just wondering...
>> Any help much appreciated.
>> __**_
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>>
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to interrupt a long running update without roll back?

[RSmith]

On 2013/06/23 00:15, Simon Slavin wrote:

//...
UPDATE myTable SET C1 = [calculation] WHERE rowid > [lastupdatedrowid]

then update the updateprogress table.  Since there is no searching needed, 
there’s no need for scanning the table, and no need for any indexes.

Simon.


One caution on this approach - I once made a logger which logged to SQLite, several devices logging once a second, so quite a 
well-paced consumption of rowids.
Another app/thread came in and archived anything older than 6 months by reading, compressing, deleting (to keep querying to 
acceptable speeds).

Another thread still validated/updated logged readings - this one used this scheme 
of 'WHERE rowid > '+lastCheckedRowID;

It all worked perfectly but then went horribly wrong at some point after rowids consumed all of 32 bits (I think), the next rowids 
were not even starting at the beginning again (where stuff were deleted first), it seemed almost random at times... Brought a whole 
system to its knees. (No catastrophic failures, just locking up in forever-running queries etc). I just changed how the system 
worked at the time, did not have time to research the problem more, so no great advice on what to do if it happens.


Not sure if this is still the behaviour with newest version of SQLite - maybe 
someone can confirm.


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

[sqlite] VACUUM and PRAGMA temp_store

[Mario M. Westphal]

Hello List
 
the SQLite databases I use on Windows can become fairly large (several GB).
I just noticed that running a VACCUM on such a large database (where several
of the tables are also real large) can cause excessive memory usage (more
than 2.5 GB RAM in peak).
 
I tracked this down to using  "PRAGMA temp_store=MEMORY" when opening the
database.
 
When I change this to 
 
"PRAGMA temp_store=DEFAULT" or "PRAGMA temp_store=FILE"
 
the VACUUM requires almost no RAM, even for large databases.
 
 
Question:
 
Can I change PRAGMA temp_store for an open database before I run the VACUUM?
I would set it to FIILE before and back to MEMORY afterwards.
 
Is PRAGMA temp_store useful at all when used on Windows? Or can one rely on
the Windows built-in file system cache?
 
When creating/opening a file in Windows, an application can mark this file
as "temporary" (flag: FILE_ATTRIBUTE_TEMPORARY) and Windows will try to keep
it in memory if sufficient memory is available, avoiding all writes to the
medium. Does SQLite use this feature on Windows when it creates temporary
files?
 
 
Thanks for your support.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unique id for table

[RSmith]

There is of course no straight-forward way to do this, as I've noted Simon said already. But my curiosity is very piqued - Mind 
sharing what kind of application requires number-only calling? You using this on a calculator of sorts? - If so (or otherwise), some 
details would be interesting to know. (I do some embedded systems, never tried SQLite on it but might, hence the interest).


I reply On-list as others might be interested, but will take the discussion off-list as it may not really be in the scope of the 
list (I think) if your awaited reply prompts further discussion.


Have a great day!

On 2013/06/22 20:27, e-mail mgbg25171 wrote:

I need to access a row just using numbers.
The row id's fine but can I specify the table using a numeric id too?
If not I'll just create a look-up table so there's no problem.
I'm just wondering...
Any help much appreciated.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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