Re: [sqlite] formating yyyymmdd dates
you can store you dates like integers using time(). So you can use strftme
without any string operation.
[]'s
Victor
Lawrence Chitty <[EMAIL PROTECTED]> escreveu:
DuenosEnLaWEB.com.ar wrote:
>hi
>
>I have this format for dates: mmdd
>I want this format to be returned from a query: dd/mm/
>
>The only way i have found is doing this... but i do not like it very =
>much
>
>SELECT =
>strftime('%d/%m/%Y',substr('20050605',1,4)||'-'||substr('20050605',5,2)||=
>'-'||substr('20050605',7,2));
>
>is there a better way to get it?
>
>
Why not just
SELECT
substr('20050605',7,2)||'/'||substr('20050605',5,2)||'/'||substr('20050605',1,4)
Lawrence
>thanks
>
>
>
>
>
>No virus found in this incoming message.
>Checked by AVG Anti-Virus.
>Version: 7.0.344 / Virus Database: 267.10.18/86 - Release Date: 31/08/2005
>
>
"Só existem 10 tipos de pessoas, as que sabem e as que não sabem binário."
-
Yahoo! Acesso Grátis: Internet rápida e grátis. Instale o discador agora!
Re: [sqlite] formating yyyymmdd dates
DuenosEnLaWEB.com.ar wrote:
hi
I have this format for dates: mmdd
I want this format to be returned from a query: dd/mm/
The only way i have found is doing this... but i do not like it very =
much
SELECT =
strftime('%d/%m/%Y',substr('20050605',1,4)||'-'||substr('20050605',5,2)||=
'-'||substr('20050605',7,2));
is there a better way to get it?
Why not just
SELECT
substr('20050605',7,2)||'/'||substr('20050605',5,2)||'/'||substr('20050605',1,4)
Lawrence
thanks
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.10.18/86 - Release Date: 31/08/2005
Re: [sqlite] unixepoch seconds issue?
Duh! :-))
Thanks!
--- "D. Richard Hipp" <[EMAIL PROTECTED]> wrote:
> On Wed, 2005-08-31 at 15:46 -0700, Clark Christensen
> wrote:
> > Using Sqlite v3.2.5 on both Linux, and Windows, I'm
> having
> > some difficulty reconciling unixepoch seconds with
> other
> > tools. Consider this:
> >
> > select strftime('%s', '2005-08-30 15:19:00');
> >
> > returns 1125415140
> >
> > If I take that resulting value and feed it to
> localtime()
> > in Perl, either under Windows or Linux, the resulting
> > date/time value is "2005-08-30 08:19:00"
> >
>
> SQLite works in UTC (aka GMT) not in localtime.
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
>
Re: [sqlite] unixepoch seconds issue?
Clark Christensen wrote:
Using Sqlite v3.2.5 on both Linux, and Windows, I'm having
some difficulty reconciling unixepoch seconds with other
tools. Consider this:
select strftime('%s', '2005-08-30 15:19:00');
returns 1125415140
This is the UTC time (sort of the same as GMT I think). You probably
want to use the localtime modifier here, e.g
select strftime('%s', '2005-08-30 15:19:00' , 'localtime');
If I take that resulting value and feed it to localtime()
in Perl, either under Windows or Linux, the resulting
date/time value is "2005-08-30 08:19:00"
The same thing happens using a JavaScript Date object:
It's similar with other date strings, but the difference in
the hours isn't always the same.
Daylight savings or whatever you local equivalent is to British Summer
Time ??
Lawrence
Re: [sqlite] unixepoch seconds issue?
On Wed, 2005-08-31 at 15:46 -0700, Clark Christensen wrote:
> Using Sqlite v3.2.5 on both Linux, and Windows, I'm having
> some difficulty reconciling unixepoch seconds with other
> tools. Consider this:
>
> select strftime('%s', '2005-08-30 15:19:00');
>
> returns 1125415140
>
> If I take that resulting value and feed it to localtime()
> in Perl, either under Windows or Linux, the resulting
> date/time value is "2005-08-30 08:19:00"
>
SQLite works in UTC (aka GMT) not in localtime.
--
D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] formating yyyymmdd dates
hi
I have this format for dates: mmdd
I want this format to be returned from a query: dd/mm/
The only way i have found is doing this... but i do not like it very =
much
SELECT =
strftime('%d/%m/%Y',substr('20050605',1,4)||'-'||substr('20050605',5,2)||=
'-'||substr('20050605',7,2));
is there a better way to get it?
thanks
Re: [sqlite] unixepoch seconds issue?
> 2005-08-30 15:19:00 > 2005-08-30 08:19:00 Your time zone is -7. Read the SQLite docs to see how to convert between local time and UTC (they're in the wiki). Regards
Re: [sqlite] unixepoch seconds issue?
Would this be covered by the change in
http://www.sqlite.org/cvstrac/tktview?tn=1216 and its
related check-in?
--- Clark Christensen <[EMAIL PROTECTED]> wrote:
> Using Sqlite v3.2.5 on both Linux, and Windows, I'm
> having
> some difficulty reconciling unixepoch seconds with other
> tools. Consider this:
>
> select strftime('%s', '2005-08-30 15:19:00');
>
> returns 1125415140
>
> If I take that resulting value and feed it to localtime()
> in Perl, either under Windows or Linux, the resulting
> date/time value is "2005-08-30 08:19:00"
>
> The same thing happens using a JavaScript Date object:
>
> var epochdate = new Date(1125415140*1000);
> document.write(epochdate.toString());
>
> prints "Tue Aug 30 08:19:00 PDT 2005" in MSIE under
> Windows.
>
> Server-side JavaScript (Solaris on the server)
>
> var epochdate = new Date(1125415140*1000);
> Response.write(epochdate.toString());
>
> prints "Tue Aug 30 08:19:00 GMT-0700 (PDT) 2005".
>
> It's similar with other date strings, but the difference
> in
> the hours isn't always the same.
>
> Am I missing something?
>
> Thanks!
>
> -Clark
>
>
[sqlite] unixepoch seconds issue?
Using Sqlite v3.2.5 on both Linux, and Windows, I'm having
some difficulty reconciling unixepoch seconds with other
tools. Consider this:
select strftime('%s', '2005-08-30 15:19:00');
returns 1125415140
If I take that resulting value and feed it to localtime()
in Perl, either under Windows or Linux, the resulting
date/time value is "2005-08-30 08:19:00"
The same thing happens using a JavaScript Date object:
var epochdate = new Date(1125415140*1000);
document.write(epochdate.toString());
prints "Tue Aug 30 08:19:00 PDT 2005" in MSIE under
Windows.
Server-side JavaScript (Solaris on the server)
var epochdate = new Date(1125415140*1000);
Response.write(epochdate.toString());
prints "Tue Aug 30 08:19:00 GMT-0700 (PDT) 2005".
It's similar with other date strings, but the difference in
the hours isn't always the same.
Am I missing something?
Thanks!
-Clark
Re: [sqlite] Problems with threadsafe opt correction #2623
> This works on some systems but not on others. On some > versions of Linux, a thread is not able to override locks > created by a different thread in the same process. When > that happens, a database connection created on one thread > will not be usable by a different thread. This is not the point here because the file lock should be released to allow the previous OsFile->tid to be reset (this is my case). (i forgot this from the enhancement...) 1: - "sqlite3_release" function. It checks the lock state of files. If all is clear, set OsFile->tid to 0. return the state. 1-bis: - "sqlite3_release" function. It checks the lock state of files. If something is locked, it releases transactions/file locks and finally set OsFile->tid to 0. 2: - Atomically, a thread try to 'CHECK_THREADID' in sqlite3OsLock and if the OsFile->tid eq 0, just call SET_THREADID. Best regards, -- Guillaume FOUGNIES Eulerian Technologies
Re: [sqlite] Problems with threadsafe opt correction #2623
Hello, Is this the only reason for the... "database handle can only be used on the same thread that opened it" ...rule? Does Windows have that issue? If so, can we convert this to a compile time option? Eg. OS_SUPPORTS_THREADSAFE_FILE_LOCKS or similar flag which can be set to true for OSes which do not have this issue? My application is Windows only. It's also a component that can be passed around between threads. Hence I have to do a 'thread-id check' on *every* method call into the component. This rule also forces us to pass around the database filename and path rather than the handle. Since on any call, if the 'thread-id check' fails, the database has to be re-opened. Regards, Kervin D. Richard Hipp wrote: On Wed, 2005-08-31 at 12:53 +0200, Guillaume Fougnies wrote: My code is sharing a pool of SQLite connections on multiple databases between a bunch of treatment threads. Each thread pops a connection from the pool safely and push it back once finished. This works on some systems but not on others. On some versions of Linux, a thread is not able to override locks created by a different thread in the same process. When that happens, a database connection created on one thread will not be usable by a different thread. Additional information: http://www.sqlite.org/cvstrac/tktview?tn=1272 http://www.sqlite.org/cvstrac/chngview?cn=2521
[sqlite] Using indexes to search null and non-null values
Hi, How can I use an index to search for null and non-null values? I have the following table (with one million rows): CREATE TABLE tbl (id string primary key unique, time); CREATE INDEX time_ind on tbl(time); It seems that: select * from tbl where id notnull; does not use an index and takes a 'long' time. On the other hand select * from tbl where id > ''; returns immediately and uses an index. Are these two queries equivalent? Similarly, select * from tbl where id isnull; does not use an index and takes a 'long' time. Is there an indexed alternative? Thanks, Ron
Re: [sqlite] Problems with threadsafe opt correction #2623
On Wed, 2005-08-31 at 12:53 +0200, Guillaume Fougnies wrote: > My code is sharing a pool of SQLite connections on > multiple databases between a bunch of treatment > threads. > Each thread pops a connection from the pool safely > and push it back once finished. > This works on some systems but not on others. On some versions of Linux, a thread is not able to override locks created by a different thread in the same process. When that happens, a database connection created on one thread will not be usable by a different thread. Additional information: http://www.sqlite.org/cvstrac/tktview?tn=1272 http://www.sqlite.org/cvstrac/chngview?cn=2521 -- D. Richard Hipp <[EMAIL PROTECTED]>
RE: [sqlite] Simple question
To avoid unintentionally finding indexes with the given name, you should use: select * from sqlite_master where type = 'table' and name = 'TABLENAME' -Tom > -Original Message- > From: Sergey Startsev [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 31, 2005 7:35 AM > To: Massimo Gaspari > Subject: Re: [sqlite] Simple question > > Hello Massimo, > > Wednesday, August 31, 2005, 7:19:27 PM, you wrote: > > MG> Is there any way to check if a table (in an opened > database) exists? > MG> Something smarter than "SELECT * FROM TABLENAME". ? > > Select * from sqlite_master where name = 'TABLENAME' > > -- > Best regards > > Sergey Startsev > SQLite Analyzer - SQLite database management tool with GUI. > http://www.kraslabs.com/sqlite_analyzer.html > >
Re: [sqlite] Simple question
Hello Massimo, Wednesday, August 31, 2005, 7:19:27 PM, you wrote: MG> Is there any way to check if a table (in an opened database) exists? MG> Something smarter than "SELECT * FROM TABLENAME". ? Select * from sqlite_master where name = 'TABLENAME' -- Best regards Sergey Startsev SQLite Analyzer - SQLite database management tool with GUI. http://www.kraslabs.com/sqlite_analyzer.html
[sqlite] Simple question
Dear all, Is there any way to check if a table (in an opened database) exists? Something smarter than "SELECT * FROM TABLENAME". ? The query should be carried out without any info about the table contents. Regards Max
[sqlite] Problems with threadsafe opt correction #2623
Hello, After an upgrade from sqlite 3.2.2 to 3.2.5, i got problems with strange SQLITE_MISUSE returns. After a little investigation, i found it was the --enable-threadsafe option correction in Check-In 2623. It seems SQLite, in os_unix.c sqlite3OsLock with the SQLITE_UNIX_THREADSAFE flag (corrected), force a connection to be always used by the same thread. The SET_THREADID is only done during an 'open'. My code is sharing a pool of SQLite connections on multiple databases between a bunch of treatment threads. Each thread pops a connection from the pool safely and push it back once finished. So i get a SQLITE_MISUSE return from sqlite3OsLock when a second thread try to use safely an unused connection. Here is a possible way for the enhancement: - a "release" function called at the end of treatment to set the SQLite OsFile->tid to 0. - Atomically, a thread try to 'CHECK_THREADID' in sqlite3OsLock and if the OsFile->tid eq 0, just call SET_THREADID. Bye, -- Guillaume FOUGNIES Eulerian Technologies
Re: [sqlite] Running in memory only
On 8/31/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > I would like to know if it is possible to have an instance of sqlite > running in RAM only? My aim is to build a small but fast memory SQL > storage. How about using the database called ":memory:". Unless this has been removed when I wasn't looking, this would probably be the "easiest" way. -- Joel Lucsy "The dinosaurs became extinct because they didn't have a space program." -- Larry Niven
Re: [sqlite] Running in memory only
Thanks guys I will investigate those features. Benjamin Filippi Capital Fund Management 6 boulevard Haussmann 75009 Paris Tel: +33 1 49 49 59 30 Fax: +33 1 47 70 17 40 [EMAIL PROTECTED] http://www.cfm.fr Ben Clewett <[EMAIL PROTECTED]> 08/31/2005 10:41 AM Please respond to sqlite-users To: sqlite-users@sqlite.org cc: Subject:Re: [sqlite] Running in memory only Benjamin, This works well on Linux using file system /dev/shm. This should be installed as default and works indistinguishably from normal file system. SQLite writes about 200 times faster than normal file system. Remember that like all ram disks, this is wiped without warning on shut-down. So consider it only for duplicate data. I also find having an 'rc' script to re-create tables on startup is a great help. David Pitcher wrote: > Salut Benjamin, > > Simplest way to make that happen is to use a ram disk, google for > 'RamDisk' and the platform you are using. Then you can use the existing > sqlite binary/library but all I/O occurs in memory. > > When you want to persist, you just copy it to a hard disk... > > D. >
Re: [sqlite] Running in memory only
Benjamin, This works well on Linux using file system /dev/shm. This should be installed as default and works indistinguishably from normal file system. SQLite writes about 200 times faster than normal file system. Remember that like all ram disks, this is wiped without warning on shut-down. So consider it only for duplicate data. I also find having an 'rc' script to re-create tables on startup is a great help. David Pitcher wrote: Salut Benjamin, Simplest way to make that happen is to use a ram disk, google for 'RamDisk' and the platform you are using. Then you can use the existing sqlite binary/library but all I/O occurs in memory. When you want to persist, you just copy it to a hard disk... D.
Re: [sqlite] Running in memory only
Salut Benjamin, Simplest way to make that happen is to use a ram disk, google for 'RamDisk' and the platform you are using. Then you can use the existing sqlite binary/library but all I/O occurs in memory. When you want to persist, you just copy it to a hard disk... D.
[sqlite] Running in memory only
Hi, I would like to know if it is possible to have an instance of sqlite running in RAM only? My aim is to build a small but fast memory SQL storage. Thanks. Benjamin Filippi Capital Fund Management 6 boulevard Haussmann 75009 Paris Tel: +33 1 49 49 59 30 Fax: +33 1 47 70 17 40 [EMAIL PROTECTED] http://www.cfm.fr
