On Tue, Apr 17, 2012 at 10:45 AM, Mark Jones <m...@jonesgroup.co.uk> wrote:
> Afternoon all,
>
> I have the following schema:
>
> CREATE TABLE day
> ("id" INTEGER PRIMARY KEY,
> "timestamp" DATETIME,
> "value" REAL);
>
> 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");
>
> 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
>
> Why does this happen? Is this the sort of thing I should bother the SQLite
> develops with (via bug report)?
Because you're doing *string* comparison of strings that represent
date, on the one hand, and date + time on the other. SQLite3 does not
have a native date/time type, and so has no native date/time
comparison operator either. You can still do the comparison you want,
but you have to do it slightly differently. Here's one option:
SELECT * FROM day WHERE datetime(timestamp) >= '2012-01-01 00:00:00';
Another option would be to convert to seconds since the Unix epoch and
then use integer comparison:
SELECT * FROM day WHERE strftime('%s', timestamp) >=
strftime('%s', '2012-01-01 00:00:00');
See http://sqlite.org/lang_datefunc.html
Nico
--
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
