Re: [sqlite] Question limit use for me of sqlite, I need help, please, Marcelo Paiva, Brasil

[Simon Slavin]

On 22 May 2012, at 7:25pm, Marcelo Paiva  wrote:

> data -> date type -> example -> 22/05/2012 -> diferent -> "22/05/2012"
> 
> question/sentence: select *from tcontsif01 where data>='01/01/2012' and
> data<='01/05/2012'

SQLite has no datatype for dates.  Your data '01/01/2012' and '22/05/2012' is 
being stored as text.  So a test

'31/12/2012' > '01/01/2013'

is true because of the first characters: '31' > '01' like 'hut' > 'house' in a 
dictionary.

I recommend that when you store dates in your database you store them in a 
sortable order.  For instance you can store the date '22/05/2012' as 
'2012/05/22'.  If you do this, then a SELECT command

SELECT * FROM tcontsis01 WHERE data >= '2012/01/01' AND data <= '2012/05/01'

will work perfectly.  An alternative would be to store dates as day numbers, 
either using unix epoch (real numbers) or Julian date numbers (integers).  In 
all these cases you search will, of course, be faster if you have the 'data' 
column in an index.

If changing the format of the data in your table is very difficult, then you 
can continue to store them in their current order and use SQLite's date 
functions in your SELECT commands:



However, this will make searches far slower because SQLite will have to convert 
each data entry as it does each search every time it does a search.

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

Re: [sqlite] Question limit use for me of sqlite, I need help, please, Marcelo Paiva, Brasil

[Chris Peachment]

Here are the steps you must take:

1. Understand that sqlite3 does not have a DATE type, only text.
   It does have functions that can work with text strings to
   be treated as dates, for example strftime().

2. Convert all your dates in the database and in your programmes
   to use a text format with parts in descending order. This is
   ISO 8601 (http://pt.wikipedia.org/wiki/ISO_8601)

 year - month - day
 (ano - mês - dia)

3. Use 4 digit years like 2012 and not 12.

4. Now it is possible to compare dates in the way you want to do.

On Tue, 2012-05-22 at 15:25 -0300, Marcelo Paiva wrote:
> Marcelo Paiva, home user, I am bazilian, I don´t speak or write English, I
> need help around sqlite?, my question:
> 
> How question in sqlite the sentence like  the sentence in Posthe tgreSQL:
> 
> data -> date type -> example -> 22/05/2012 -> diferent -> "22/05/2012"
> 
> question/sentence: select *from tcontsif01 where data>='01/01/2012' and
> data<='01/05/2012'
> 
> in sqlite ?? not type data, only TEXT -> in sqlite 22/05/2012
> like,iqual "22/05/2012" or '22/05/2012', the filter negative, order by
> negative,
> 
> negative -> select *from tcontsif01 where data>='01/01/2012' and
> data<='01/05/2012'
> 
> order by data -> negative -> order by ASCII in sqlite
> 
> please, one light, please solution, please example, please help me
> 
> I understand datetime, understand strtime please help me, please a example
> routine sql for sqlite3 with functions,
> 
> Marcelo Paiva
> Stante Santa Catarina - Brasil - here is not tropical here is subtropical
> here is cold, but here is Brasil ... please help me
> ___
> 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] Question limit use for me of sqlite, I need help, please, Marcelo Paiva, Brasil

[Marcelo Paiva]

Marcelo Paiva, home user, I am bazilian, I don´t speak or write English, I
need help around sqlite?, my question:

How question in sqlite the sentence like  the sentence in Posthe tgreSQL:

data -> date type -> example -> 22/05/2012 -> diferent -> "22/05/2012"

question/sentence: select *from tcontsif01 where data>='01/01/2012' and
data<='01/05/2012'

in sqlite ?? not type data, only TEXT -> in sqlite 22/05/2012
like,iqual "22/05/2012" or '22/05/2012', the filter negative, order by
negative,

negative -> select *from tcontsif01 where data>='01/01/2012' and
data<='01/05/2012'

order by data -> negative -> order by ASCII in sqlite

please, one light, please solution, please example, please help me

I understand datetime, understand strtime please help me, please a example
routine sql for sqlite3 with functions,

Marcelo Paiva
Stante Santa Catarina - Brasil - here is not tropical here is subtropical
here is cold, but here is Brasil ... please help me
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users