Re: [sqlite] About Time Field

[John Stanton]

Sqlite does not have a TIME type..  It is interpreting your time as an 
integer or floating point number or maybe text, depending upon its format.

Hughman wrote:
> Hi,
>
> I create a table with a field of datatype Time, and when I insert a
> formatting string like 'HHMMSS' into it , the first number 0 always be
> trimed . I want to keep it, how should I do?
> For exampe, '081220' will be converted into '81220'.
>
>
> 
>Say goodbye to romance...
> ___
> 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] About Time Field

[cmartin]

On Fri, 15 May 2009, Hughman wrote:

>> There is no Time type in SQLite.
>
> Oops... I use Sqlite Administrator to create a table , and the datatypes are
> almost as many as MySQL , such as Date, Time, TimeStamp, varchar.
> Since sqlite only has 5 kinds of datatype, why doesn't it throw a error
> message when I create a table with a wrong datatype?

Igor previously indicated (above) that there is no time type in SQLIte. 
However, he also said:

> For more details, see http://sqlite.org/datatype3.html

Based on your question about errors not being thrown, you apparently did 
not read the above link for the details; if you had you would realize no 
error is thrown because no error has occurred. SQLite does not use static 
typing, like mySQL and most others, it uses manifest typing.

Read the link and all will be clear.

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

Re: [sqlite] About Time Field

[Igor Tandetnik]

Hughman  wrote:
>> There is no Time type in SQLite.
>
> Oops... I use Sqlite Administrator to create a table , and the
> datatypes are
> almost as many as MySQL , such as Date, Time, TimeStamp, varchar.
> Since sqlite only has 5 kinds of datatype, why doesn't it throw a
> error
> message when I create a table with a wrong datatype?

In SQLite, you can write

create table t(col LOREM IPSUM);

and it will happily accept LOREM IPSUM as the type name. The article I 
referred you to explains how column affinity is determined from type 
name. TIME is not in any way special.

>> I bet you don't actually use quotes as you show above.
> In fact, I have used in the sql code.

Ah, right. The type name of TIME would result in NUMERIC column 
affinity. This means that a string that looks like a number is converted 
to and stored as a number.

Use TEXT, CHAR or VARCHAR for a type name to get TEXT affinity, or omit 
the type name entirely to get no affinity.

Igor Tandetnik 



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

Re: [sqlite] About Time Field

[Hughman]

Thanks.

> There is no Time type in SQLite.

Oops... I use Sqlite Administrator to create a table , and the datatypes are
almost as many as MySQL , such as Date, Time, TimeStamp, varchar.
Since sqlite only has 5 kinds of datatype, why doesn't it throw a error
message when I create a table with a wrong datatype?

> I bet you don't actually use quotes as you show above.
In fact, I have used in the sql code.

   Say goodbye to romance...


On Fri, May 15, 2009 at 8:37 PM, Igor Tandetnik  wrote:

> "Hughman"  wrote in
> message
> news:f1a32add0905150528r3bc74b2epd7ab93539ac68...@mail.gmail.com
> > I create a table with a field of datatype Time
>
> There is no Time type in SQLite. For more details, see
> http://sqlite.org/datatype3.html
>
> > and when I insert a
> > formatting string like 'HHMMSS' into it , the first number 0 always be
> > trimed .
>
> I bet you don't actually use quotes as you show above. In which case,
> what you store is an integer. Naturally, 012345 = 12345.
>
> Consider storing a string in HH:MM:SS format instead. That would allow
> you to use built-in date/time functions if you ever need to perform time
> arithmetic on this field: http://sqlite.org/lang_datefunc.html
>
> Igor Tandetnik
>
>
>
> ___
> 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] About Time Field

[Igor Tandetnik]

"Hughman"  wrote in
message
news:f1a32add0905150528r3bc74b2epd7ab93539ac68...@mail.gmail.com
> I create a table with a field of datatype Time

There is no Time type in SQLite. For more details, see 
http://sqlite.org/datatype3.html

> and when I insert a
> formatting string like 'HHMMSS' into it , the first number 0 always be
> trimed .

I bet you don't actually use quotes as you show above. In which case, 
what you store is an integer. Naturally, 012345 = 12345.

Consider storing a string in HH:MM:SS format instead. That would allow 
you to use built-in date/time functions if you ever need to perform time 
arithmetic on this field: http://sqlite.org/lang_datefunc.html

Igor Tandetnik



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