On 17 Apr 2012, at 4:45pm, Mark Jones <m...@jonesgroup.co.uk> wrote:
> I have the following schema:
>
> CREATE TABLE day
> ("id" INTEGER PRIMARY KEY,
> "timestamp" DATETIME,
> "value" REAL);
There is no such datatype as 'DATETIME'. You are actually storing text. See
especially section 1.2, but possibly the whole page of
<http://www.sqlite.org/datatype3.html>
> And the following sample data:
>
> INSERT INTO day VALUES (NULL, "2012-01-01", "5.0");
> INSERT INTO day VALUES (NULL, "2012-01-02", "6.0");
> INSERT INTO day VALUES (NULL, "2012-01-03", "7.0");
> INSERT INTO day VALUES (NULL, "2012-01-04", "5.0");
For text in SQLite, delimit with single quotes, not double quotes. Double
quotes are used for tricky entity names. And you probably don't want the
quotes around the real numbers at all.
> When I perform the following query I don't get the expected result:
>
> SELECT * FROM day WHERE timestamp >= '2012-01-01 00:00:00';
> 2|2012-01-02|6.0
> 3|2012-01-03|7.0
> 4|2012-01-04|5.0
The string '2012-01-01' sorts before the string '2012-01-01 00:00:00'. Your
problem is that your strings are being understood and stored as strings, not a
way of using a string to specify a timestamp. You could specify times in your
INSERT commands ...
INSERT INTO day VALUES (NULL, '2012-01-01 00:00:00', 5.0);
or you might choose to store just dates as you already do, and do your SELECT
using just the date:
SELECT * FROM day WHERE timestamp >= '2012-01-01';
but since you call the field "timestamp" you probably want to figure times in
it.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
