1

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?

Joao Figueiredo
  • 3,120
  • 3
  • 31
  • 40
Irina Shirinsky
  • 11
  • 1

2 Answers2

0

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;
Joao Figueiredo
  • 3,120
  • 3
  • 31
  • 40
  • 1
    This does not work for me. Always get an error: "Only literals supported as RHS, found: com.yahoo.platforms.yql.Expression$FunctionExpression@21d43a5b expecting literal" – Irina Shirinsky Jun 25 '12 at 19:12
  • Is the url correct? Trying the url in the browser gives a 404. I suspect its 'http://finance.yahoo.com/news/rss – Karan Ashar Jun 25 '12 at 21:13
0

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 :)

Karan Ashar
  • 1,392
  • 1
  • 10
  • 23
  • That's why a function like date_trunc is your best friend here. – Joao Figueiredo Jun 26 '12 at 09:31
  • Thanks for your reply, I try it in YQL Console like this: – Irina Shirinsky Jun 26 '12 at 13:02
  • Thanks for your reply, I try it in YQL Console like this: select * from rss where url='http://finance.yahoo.com/news/rss' and item.pubDate>='2012-06-26' | sort(field="pubDate", descending="true") and it still return all of them. I understand that is because pubDate is a string but could not find how to convert it to datetime type in YQL Console. Function date_trunc() does not work in this query too, I get error listed below about "expecting literal". – Irina Shirinsky Jun 26 '12 at 13:10
  • I found the way as using "like" operator select * from rss where url='http://finance.yahoo.com/news/rss' and (pubDate like 'Mon, 25%' or pubDate like 'Tue, 26%')| sort(field="pubDate", descending="true"). BUT looking for something simpler. – Irina Shirinsky Jun 26 '12 at 13:11