How to query YQL rss table by pubDate

Question

I would like to get yahoo finance news for last 3 days, something like that

select * from rss where url='http://finance.yahoo.com.news/rss' and pubDate >= '2012-06-23'

BUT this part "pubDate >= '2012-06-23'" is ignored. I always get the same results with or without this clause. What is the right syntax to make it work?

Answer 1

Irina,
you're using a 'greater than' operator over a string,
to filter by pubDate you'll have to use something like,

and pubDate >= date_trunc('month',current_date)
order by pubdate desc;

Answer 2

The right query would be

select * from rss where url='http://finance.yahoo.com/news/rss' and item.pubDate>='2012-06-26'

Notice the item.pubDate. The reason for item. is because in the xml structure, pubDate is a child of item.

Unfortunately, this is not going to work for your use case (sort by time). That's because the pubDate returned is not in unix timestamp. Sorry I do not know what needs to be done in order for you to get the right results but atleast I could give you the right query :)