I find that I must cast the result of a '%w' modifier to INTEGER to use it
in a select.
I have a table of TV programs that has title and an integer representing the
show's start time as unix epoch.
This does not produce any results, though I have shows beginning on Monday:
select title, datetime(start_time,'unixepoch','localtime') from show_list
where strftime('%w',start_time,'unixepoch','localtime') = 1;
I find if I create a temp table:
create temp table foo as select title, start_time,
strftime('%w',start_time,'unixepoch','localtime') dow from show_list;
I get an odd schema where there is no data type for the dow column:
.schema foo
sqlite> .schema foo2
CREATE TABLE foo2(title_long TEXT,start_time INTEGER,dow);
sqlite>
And that
select * from foo where dow = 1;
still gives me no results.
However, if I
create temp table foo2 as select title, start_time,
cast(strftime('%w',start_time,'unixepoch','localtime') as integer) dow from
show_list;
I get results from the query for dow = 1 on foo2.
Similarly, I can use the cast in the earlier query and get a result set. I
can also use the un-cast strftime('%w'...) in a group by clause, which gives
me expected results.
But this all seems odd, given how forgiving SQLite is regarding data types.
It appears at first blush that because the result of the strftime('%w...)
has no type, it's not comparing correctly with my INT constant in the
query.
So the question is: What's going on? Is this expected behavior or a bug?
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
