Re: [sqlite] SQLite Date problem
P Kishor wrote: > On Sun, Sep 20, 2009 at 4:16 AM, Dan Bishop wrote: > >> Max_wang wrote: >> >>> A few months ago,I used SQLite 3.5.0 execute SQL:"SELECT >>> date(253392451200.0, >>> 'unixepoch');" >>> The result was "-09-09". >>> >>> But now I use SQLite 3.6.18 replace it,this SQL execute result is >>> "-1413-03-01". >>> >>> Is this a Bug? >>> >>> >> Apparently so. And the problem first occurs in 5352. >> >> sqlite> SELECT date(106751991167.30063, 'unixepoch'); >> 5352-11-01 >> >> sqlite> SELECT date(106751991167.30064, 'unixepoch'); >> -1413-03-01 > How on earth did you discover that?! The Bisection Method. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Date problem
On Sun, Sep 20, 2009 at 4:16 AM, Dan Bishop wrote: > Max_wang wrote: >> A few months ago,I used SQLite 3.5.0 execute SQL:"SELECT date(253392451200.0, >> 'unixepoch');" >> The result was "-09-09". >> >> But now I use SQLite 3.6.18 replace it,this SQL execute result is >> "-1413-03-01". >> >> Is this a Bug? >> > Apparently so. And the problem first occurs in 5352. > > sqlite> SELECT date(106751991167.30063, 'unixepoch'); > 5352-11-01 > > sqlite> SELECT date(106751991167.30064, 'unixepoch'); > -1413-03-01 > How on earth did you discover that?! -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Date problem
Max_wang wrote: > A few months ago,I used SQLite 3.5.0 execute SQL:"SELECT date(253392451200.0, > 'unixepoch');" > The result was "-09-09". > > But now I use SQLite 3.6.18 replace it,this SQL execute result is > "-1413-03-01". > > Is this a Bug? > Apparently so. And the problem first occurs in 5352. sqlite> SELECT date(106751991167.30063, 'unixepoch'); 5352-11-01 sqlite> SELECT date(106751991167.30064, 'unixepoch'); -1413-03-01 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite Date problem
A few months ago,I used SQLite 3.5.0 execute SQL:"SELECT date(253392451200.0, 'unixepoch');" The result was "-09-09". But now I use SQLite 3.6.18 replace it,this SQL execute result is "-1413-03-01". Is this a Bug? -- View this message in context: http://www.nabble.com/SQLite-Date-problem-tp25527521p25527521.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] Date problem
Eugene,
> create table test(a int, b datetime);
> insert into test values(1,'2004/1/3');
> select * from test where date(b)='2004/1/3'
> The SELECT statement returns no record.
But this works:
sqlite> select * from test where b='2004/1/3';
1|2004/1/3
The date is not encoded properly in the database. Look again at the
date formats shown on the web page.
> Similarly, the statement:
> select date(b) from test
> does not return anything either.
> Perhaps I misunderstood the usage of those functions?
Here's an example...
sqlite> insert into test values (2,datetime("now","localtime"));
sqlite> select * from test where date(b) = "2004-01-03";
2|2004-01-03 22:28:56
sqlite>
e
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Date problem
You won't find /MM/DD in the list of legal time strings. By the way, the developer(s) intend for dates to be stored as floats (in tables). Try select date(2453007.5); - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Date problem
Kurt, Thanks for your message. >>date() returns NULL because you're giving it an illegal time string Well, I thought the date (not the time) string I'm giving is correct, e.g. "2004/1/3'. It is in the correct format "/MM/DD". I am not interested in the DATE part, that is why I'm calling "date()" function instead of "datetime()". Regards Eugene *** REPLY SEPARATOR *** On 01/03/2004 at 5:12 PM Kurt Welgehausen wrote: >There seem to be some problems with the date/time functions, >but your statements will never work. Your select returns >NULL because date() returns NULL. date() returns NULL >because you're giving it an illegal time string. Read the >docs again, and pay attention to the list of legal time >strings. > >Regards > >- >To unsubscribe, e-mail: [EMAIL PROTECTED] >For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Date problem
There seem to be some problems with the date/time functions, but your statements will never work. Your select returns NULL because date() returns NULL. date() returns NULL because you're giving it an illegal time string. Read the docs again, and pay attention to the list of legal time strings. Regards - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Date problem
Hi, With regard to these new date/time functions as pointed out at: http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions I have one question: I can't seem to be able to use them on actual datetime field in the database. For instance, my test script as below: create table test(a int, b datetime); insert into test values(1,'2004/1/3'); select * from test where date(b)='2004/1/3' The SELECT statement returns no record. Similarly, the statement: select date(b) from test does not return anything either. Perhaps I misunderstood the usage of those functions? Thanks for any help. Eugene Lin *** REPLY SEPARATOR *** On 01/01/2004 at 10:38 PM D. Richard Hipp wrote: >KL Chin wrote: >> >> Is that away to have a "DATE" comparison inside SQLite? >> Or any expression to convert DATE to integer in SQLite? >> I mean, I don;t have to worry about data migration from other >> database. >> > >http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions > > >-- >D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 > > >- >To unsubscribe, e-mail: [EMAIL PROTECTED] >For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Date problem
Hi D. Richard Hipp, After read the document, I still no sure how can I import/convert from the other Database's data using these command sets. For example if I wanted to import data from CSV, with the date format of MM/DD/YY, how the query statement like, if I want to insert to SQLite with the Date And Time functions Insert Into ABC Values( "12/29/03","KLCHIN","Bye Bye 2003" ); Insert Into ABC Values( "1/2/04","KLCHIN","Happy New Year 2004" ); Is there any fucntion that direct convert like this Insert Into ABC Values( CvtDate(1/2/04,YY/MM/DD,YY/MM/DD) ,"KLCHIN","Happy New Year 2004" ); This funciton CvtDate was currently use to reconstruct the SQL statement b4 insert into SQLite. Thx in advanced. Regards KL Chin -Original Message- From: D. Richard Hipp [SMTP:[EMAIL PROTECTED] Sent: Friday, January 02, 2004 11:38 AM Cc: '[EMAIL PROTECTED]' Subject:Re: [sqlite] Date problem KL Chin wrote: > > Is that away to have a "DATE" comparison inside SQLite? > Or any expression to convert DATE to integer in SQLite? > I mean, I don;t have to worry about data migration from other > database. > http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Date problem
Hi D. Richard Hipp, Thx for ur help. Regards KL Chin -Original Message- From: D. Richard Hipp [SMTP:[EMAIL PROTECTED] Sent: Friday, January 02, 2004 11:38 AM Cc: '[EMAIL PROTECTED]' Subject: Re: [sqlite] Date problem KL Chin wrote: > > Is that away to have a "DATE" comparison inside SQLite? > Or any expression to convert DATE to integer in SQLite? > I mean, I don;t have to worry about data migration from other > database. > http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Date problem
KL Chin wrote: Is that away to have a "DATE" comparison inside SQLite? Or any expression to convert DATE to integer in SQLite? I mean, I don;t have to worry about data migration from other database. http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Date problem
Hi Michael, Thx for your reply. I do understand SQLite is typeless. Infact I have already done the /MM/DD format. Is that away to have a "DATE" comparison inside SQLite? Or any expression to convert DATE to integer in SQLite? I mean, I don;t have to worry about data migration from other database. Regards KL Chin -Original Message- From: Michael A. Cleverly [SMTP:[EMAIL PROTECTED] Sent: Friday, January 02, 2004 9:55 AM To: KL Chin Cc: '[EMAIL PROTECTED]' Subject: Re: [sqlite] Date problem On Fri, 2 Jan 2004, KL Chin wrote: > Can anyone help on this problem, why with the query below > > DELETE FROM ABC WHERE datein < '12/29/03'; > > All my record date from '1/1/04' to '1/6/04' were delete. > Where datein was date type. SQLite is typeless, see: http://www.sqlite.org/datatypes.html, paying particular attention to section 3.0 on "comparison and sort order." As a string, "1/1/04" is less than "12/29/03". If you store your dates in -MM-DD format, they'll sort & compare properly as you would expect. Michael - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Date problem
On Fri, 2 Jan 2004, KL Chin wrote: > Can anyone help on this problem, why with the query below > > DELETE FROM ABC WHERE datein < '12/29/03'; > > All my record date from '1/1/04' to '1/6/04' were delete. > Where datein was date type. SQLite is typeless, see: http://www.sqlite.org/datatypes.html, paying particular attention to section 3.0 on "comparison and sort order." As a string, "1/1/04" is less than "12/29/03". If you store your dates in -MM-DD format, they'll sort & compare properly as you would expect. Michael - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Date problem
Hi, Can anyone help on this problem, why with the query below DELETE FROM ABC WHERE datein < '12/29/03'; All my record date from '1/1/04' to '1/6/04' were delete. Where datein was date type. How Regards KL Chin - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
