Re: [sqlite] Possible Timestamp Where cluase bug
On 23 Jan 2012, at 3:09pm, Steffen Mangold wrote: > So SQLITE stores my timestamp I the way it was pushed in. I toughed It has a > fixed format to store it. No, it's completely ignoring timestamp and considers all those as strings. So pick either '.' or '-' and keep to it. > Simple want all events between begin and end of a day. Check the first part of the string to see it matches. You can use substr(X,1,10) Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible Timestamp Where cluase bug
> > SQLite does not have a separate "date/time" datatype. It uses either strings > (preferably in ISO8601 format) or numbers (seconds since 1970 or Julian day > number). > > Your WHERE clause is comparing strings, not dates. If you using ISO8601 > dates in your database file, as you do in the query, it will probably work, > though. > Ah ok i dont know this. So I must know the exact datetime string format with where pushed in to make a valid string compare when I do selects, right? So SQLITE stores my timestamp I the way it was pushed in. I toughed It has a fixed format to store it. Thank you. Steffen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible Timestamp Where cluase bug
> > Plus...what's the "T" supposed to do? Perhaps I'm ignorant of the magic you > expect. > The 'T' devide the date from the time. Looking here http://www.sqlite.org/lang_datefunc.html It is the default ISO-8601 datetime format. > > I'm confused as to why you would expect any match at all. And indeed, when I > run your queries against a test set I get nothing back at all for both > queries. > Simple want all events between begin and end of a day. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible Timestamp Where cluase bug
On Mon, Jan 23, 2012 at 9:29 AM, Steffen Mangold < steffen.mang...@balticsd.de> wrote: > Hi SQLITE community, > > I think i found a strange bug. > Lets say we have a table in this form: > > Id (long) | Timestamp (DateTime) > - > 12 | 17.01.2012 16:15:00 > 12 | 17.01.2012 17:15:00 > > Now we make a query where the data should involved: > > SELECT Id, TimeStamp FROM tabelA > WHERE Timestamp >= '2012-01-17T00:00:00' AND > Timestamp <= '2012-01-17T23:59:59' AND > ID = 12 > ORDER BY Timestamp DESC LIMIT 250 OFFSET 0 > > Result is 'nothing' means no rows are returned. > SQLite does not have a separate "date/time" datatype. It uses either strings (preferably in ISO8601 format) or numbers (seconds since 1970 or Julian day number). Your WHERE clause is comparing strings, not dates. If you using ISO8601 dates in your database file, as you do in the query, it will probably work, though. > > But if we change the minimum timestamp to 1 day earlier, like: > > SELECT Id, TimeStamp FROM tabelA > WHERE Timestamp >= '2012-01-16T00:00:00' AND > Timestamp <= '2012-01-17T23:59:59' AND > ID = 12 > ORDER BY Timestamp DESC LIMIT 250 OFFSET 0 > > The result is the 2 rows written above. > > ??? I don't get it ??? > Why this is happen, is it really a bug? > > > Regards > > Steffen Mangold > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible Timestamp Where cluase bug
I'm confused as to why you would expect any match at all. And indeed, when I run your queries against a test set I get nothing back at all for both queries. Your datetime formats don't match at all. And "ORDER BY" for that field is not date/time order since you have DD.MM. which won't result in datetime order. You'll get all 01 days, then 02 days, then 03 days, etc. Plus...what's the "T" supposed to do? Perhaps I'm ignorant of the magic you expect. This is what I did CREATE TABLE tableA(id,Timestamp); INSERT INTO "tableA" VALUES(12,'17.01.2012 16:15:00'); INSERT INTO "tableA" VALUES(12,'17.01.2012 17:15:00'); SELECT Id, TimeStamp FROM tableA WHERE Timestamp >= '2012-01-16T00:00:00' AND Timestamp <= '2012-01-17T23:59:59' AND ID = 12 ORDER BY Timestamp DESC LIMIT 250 OFFSET 0; --no records-- Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Steffen Mangold [steffen.mang...@balticsd.de] Sent: Monday, January 23, 2012 8:29 AM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] Possible Timestamp Where cluase bug Hi SQLITE community, I think i found a strange bug. Lets say we have a table in this form: Id (long) | Timestamp (DateTime) - 12 | 17.01.2012 16:15:00 12 | 17.01.2012 17:15:00 Now we make a query where the data should involved: SELECT Id, TimeStamp FROM tabelA WHERE Timestamp >= '2012-01-17T00:00:00' AND Timestamp <= '2012-01-17T23:59:59' AND ID = 12 ORDER BY Timestamp DESC LIMIT 250 OFFSET 0 Result is 'nothing' means no rows are returned. But if we change the minimum timestamp to 1 day earlier, like: SELECT Id, TimeStamp FROM tabelA WHERE Timestamp >= '2012-01-16T00:00:00' AND Timestamp <= '2012-01-17T23:59:59' AND ID = 12 ORDER BY Timestamp DESC LIMIT 250 OFFSET 0 The result is the 2 rows written above. ??? I don't get it ??? Why this is happen, is it really a bug? Regards Steffen Mangold ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Possible Timestamp Where cluase bug
Hi SQLITE community, I think i found a strange bug. Lets say we have a table in this form: Id (long) | Timestamp (DateTime) - 12 | 17.01.2012 16:15:00 12 | 17.01.2012 17:15:00 Now we make a query where the data should involved: SELECT Id, TimeStamp FROM tabelA WHERE Timestamp >= '2012-01-17T00:00:00' AND Timestamp <= '2012-01-17T23:59:59' AND ID = 12 ORDER BY Timestamp DESC LIMIT 250 OFFSET 0 Result is 'nothing' means no rows are returned. But if we change the minimum timestamp to 1 day earlier, like: SELECT Id, TimeStamp FROM tabelA WHERE Timestamp >= '2012-01-16T00:00:00' AND Timestamp <= '2012-01-17T23:59:59' AND ID = 12 ORDER BY Timestamp DESC LIMIT 250 OFFSET 0 The result is the 2 rows written above. ??? I don't get it ??? Why this is happen, is it really a bug? Regards Steffen Mangold ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
