For me it's pretty obvious that strftime() returns text data type
because it returns your date formatted as you like. And SQLite have
never compared text and integer as equal even if they have the same
notation when converted to text:
sqlite> select 1 where '1' = 1;
sqlite> select 1 where '1' = '1';
1
sqlite> select typeof(strftime('%w', date()));
text
sqlite>
Pavel
On Mon, Jul 27, 2009 at 11:20 AM, Bill Welsh<wel...@gmail.com> wrote:
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
