headers
Jake Skinner | 1 Dec 2003 06:33

[sqlite] Problem with time

Hello,

I seem to be having some problems with time. I am storing "17:30" into 
SQLite. When I do a 'select time(time_col) from table' I am receiving 
"05:29:59". Being one second out, I can kind of live with but, not 
returning the correct 24 hour format I can't. I'm sure I'm doing 
something wrong, however I am completely stumped as to what!

Any ideas?

Cheers
Jake

---------------------------------------------------------------------
To unsubscribe, e-mail: sqlite-users-unsubscribe@...
For additional commands, e-mail: sqlite-users-help@...
Permalink | Reply | 2 comments |
headers
Jake Skinner | 1 Dec 2003 12:50

[sqlite] Time functions

I am have declared a column in a table as 'text'. I am storing times as 
strings in this column in the format "HH:MM". I have "09:00" as a time - 
meant to be representing 9 AM. I would like to retrieve the hour, which 
I attempt with "hour(time_col_name)", what is returned is "21" - I am 
expecting "9"

I have tried three different SQLite browsers (sqliteCC, SQLiteDBBrowser 
and sqliteplus) to check my sql. All three of these return "21".

In func.c, 'getDigits' returns the expected value. ie val = 9, so how 
does it get changed to 21?

Thanks
Jake

---------------------------------------------------------------------
To unsubscribe, e-mail: sqlite-users-unsubscribe@...
For additional commands, e-mail: sqlite-users-help@...
Permalink | Reply | 0 comments |
headers
Jake Skinner | 1 Dec 2003 13:14

Re: [sqlite] Time functions

George Gensure wrote:

> What time zone are you in? (grin)  Both of your problems (17 - 5 and 9 
> - -3) are equal to 12.  sqlite may reference your local zone 
> information, and assume that values in the database are stored in gmt.

Timezone is +930, so close..... but I hadn't thought of that anyway! I 
think I will have a harder look along these lines.
Thanks :)

---------------------------------------------------------------------
To unsubscribe, e-mail: sqlite-users-unsubscribe@...
For additional commands, e-mail: sqlite-users-help@...
Permalink | Reply | 0 comments |
headers
D. Richard Hipp | 1 Dec 2003 13:18

Re: [sqlite] Problem with time

Jake Skinner wrote:
> 
> I seem to be having some problems with time. I am storing "17:30" into 
> SQLite. When I do a 'select time(time_col) from table' I am receiving 
> "05:29:59". Being one second out, I can kind of live with but, not 
> returning the correct 24 hour format I can't. I'm sure I'm doing 
> something wrong, however I am completely stumped as to what!
> 
> Any ideas?
> 

First, time functions are still considered experimental.  So do
not get too attached to them because they will probably change.

Second, the bug you are seeing was fixed in a later release
(available on the CVS server).  An even later update
completely rewrote the time functions.  The code that is
currently in the latest CVS should be what you use.

If you get the latest source code out of CVS and compile it,
things should work a lot better for you.

--

-- 
D. Richard Hipp -- drh@... -- 704.948.4565

---------------------------------------------------------------------
To unsubscribe, e-mail: sqlite-users-unsubscribe@...
For additional commands, e-mail: sqlite-users-help@...
(Continue reading)

Permalink | Reply | 1 comment |
headers
Jake Skinner | 1 Dec 2003 13:23

Re: [sqlite] Problem with time

D. Richard Hipp wrote:

>
> If you get the latest source code out of CVS and compile it,
> things should work a lot better for you.
>
Oh you beauty - I have been going nuts! :)
Thanks for that pearl of information.... I will use these experimental 
functions but be aware that I could be making problems for myself.

:)

---------------------------------------------------------------------
To unsubscribe, e-mail: sqlite-users-unsubscribe@...
For additional commands, e-mail: sqlite-users-help@...
Permalink | Reply | 0 comments |
headers
Yuval Abadi | 1 Dec 2003 13:26
Picon
Favicon

[sqlite] recover db from crash - rescue db file

Hi

I run sqlite in command line application and from some reason CTRL+C is
call while writing to data base.

I use asynchronies NORMAL.. (FULL is not possible because performance
drawbacks)

When I rerun my application I cannot load the data base file. (also
sqlite_shell is failed to load) and pragma integrity check cause
exception in sqlite dll.

My os is win2k.

Does any one know how to recover DB file?

Thanks

Yuval
Permalink | Reply | 2 comments |
headers
D. Richard Hipp | 1 Dec 2003 13:55

Re: [sqlite] recover db from crash - rescue db file

Yuval Abadi wrote:
> 
> I run sqlite in command line application and from some reason CTRL+C is
> call while writing to data base.
> 
> I use asynchronies NORMAL.. (FULL is not possible because performance
> drawbacks)

The "PRAGMA synchronous" setting is only important for recovery
from power failures and OS crashes.  It should not matter here.

On the other hand, the difference between NORMAL and FULL should be
minimal.  What kind of performance impact are you seeing?

> 
> When I rerun my application I cannot load the data base file. (also
> sqlite_shell is failed to load) and pragma integrity check cause
> exception in sqlite dll.
> 
> My os is win2k.
> 
> Does any one know how to recover DB file?
> 

The sqlite.exe shell automatically attempts to recover the
database when it runs.  If sqlite.exe failed, then the
database is unrecoverable.

My guess is that a stray pointer or buffer overrun in your
larger program overwrote some data structure inside of the
(Continue reading)

Permalink | Reply | 1 comment |
headers
Steve O'Hara | 1 Dec 2003 17:16
Picon

Re: [sqlite] Is case sensitivity a problem for me only?


> Pavel said:
> >Hi, all.
> >Traditionally, SQL databases are case insensitive, or at least have an
> >option to behave this way. Sqlite is case sensitive and this fact is
> >introduced in a lot of places in sources. Having case-insensitive
> >sorting and matching seem to be extremely useful thing to me (in
> >business domain where one have to deal with names, addresses etc). Any
> >opinions?
> >Yours, Pavel
>
> Personally, I prefer that databases *are* case-sensitive by default, as
this matches how most programming languages work.
>
> If I do a normal comparison between two strings in a programming language,
they match only if the case is the same.  It gives me plenty of headaches if
the database behaves differently, and I can't be certain if matches returned
by a database actually are matches.
>
> Also, case-sensitive comparisons (basis for both matching and sorting) are
considerably faster and use less memory than non-sensitive ones, since with
the latter one has to convert the string on each side to either an upper or
lower case representation, so that the normal numerical comparison of the
strings match, and with the former, no conversion is needed.
>
> Also, in these days of increasing internationalization, it is simpler to
do case-sensitive by default since that's what all the world's other
character sets and our own non-letter symbols do, to my knowledge.
>
> Now I do recognize the value of case-insensitive comparisions, but I think
(Continue reading)

Permalink | Reply | 0 comments |
headers
Bernie Cosell | 1 Dec 2003 19:49

[sqlite] busy_timeout from the Perl DBD interface

Is it possible to set from Perl for SQLite to wait instead of die when it 
finds the database locked?

We've been getting a few:
[09:45:23] POP3< DBD::SQLite::db do failed: database is locked at ...

and in double-checking the docs, I found:

> When SQLite tries to access a file that is locked by another process, the
> default behavior is to return SQLITE_BUSY. You can adjust this behavior
> from C code using the sqlite_busy_handler() or sqlite_busy_timeout() API
> functions.

and I'm guessing that for my application, just setting busy_timeout to 
something like 30 seconds would do the job for me, but I'm not sure 
if/how that might be doable from DBD::SQLite in Perl.

Thanks!
  /Bernie\

--

-- 
Bernie Cosell                     Fantasy Farm Fibers
mailto:bernie@...     Pearisburg, VA
    -->  Too many people, too few sheep  <--       

---------------------------------------------------------------------
To unsubscribe, e-mail: sqlite-users-unsubscribe@...
For additional commands, e-mail: sqlite-users-help@...
(Continue reading)

Permalink | Reply | 3 comments |
headers
Christian Smith | 1 Dec 2003 22:38

Re: [sqlite] iterating over all rows in a table

On Wed, 26 Nov 2003, Will Leshner wrote:

>Hi. I'm sorry if this is really basic, but I'm wondering if there is a
>way to query a table based on row number and not rowid. In other words,
>I'd like to be able to ask for row number 13 regardless of what its
>rowid might be. I'm happy to write my own function in C if anybody has
>any suggestions.

I'm really curious. Why?

Unless you're ordering the rows explicitly in the select, the order of the 
rows is undefined, and row 13 is not any more significant than row 12, for 
example.

And this is definately not portable (if that matters to you.)

>
>Thanks.
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: sqlite-users-unsubscribe@...
>For additional commands, e-mail: sqlite-users-help@...
>

--

-- 
"The problem with the French is that they don't have a word for entrepreneur"
  George W. Bush

----------------------------------------------------------------------
(Continue reading)

Permalink | Reply | 0 comments |

Gmane