Passing Timestamp in the SQL Query to filter results
I don't have a copy of D7 handy, but this seems to work on D6 for me. There's at least two problems with your code. I'm going to pare your query into a short bit of SQL, then transform it into drupal code.

Use JOINs

Whenever you're using multiple tables in SQL, you'll almost always have to use the JOIN ... ON ... syntax. Using JOIN tells MySQL that you want to connect certain rows on the two tables if a given field from those tables matches. This is a very basic SQL concept, so you'll need to master this if you want to write your own SQL or troubleshoot it. Try W3Schools tutorial for a better explanation.

Here's a simplified, pure SQL query that works for me using a JOIN:

SELECT
  n.title, n.nid, COUNT(*) as times
FROM
  node n 
JOIN
  node_view_count a
ON
    a.nid = n.nid
GROUP BY
    n.title
ORDER BY times DESC;


In my D6 installation, I have a table called node_counter, but you probably have node_view_count instead on D7. JOIN node_view_count tells MySQl that we're connecting the node table to the node_view_count table. The ON a.nid = n.nid tells MySQL to only connect rows from these two tables when the nid field matches.

WHERE Clause

Next, let's add in a simplified WHERE clause. On my D6 database, the node_counter table's timestamp field is an unix timestamp (an integer). We can do the conversion from unix timestamp to datetime using pure SQL as well (no need to use a parameter to db_query()):

...
ON
    a.nid = n.nid
WHERE
    DATE(from_unixtime(a.timestamp)) = "2012-12-21"
GROUP BY
...


Add a Paramater to db_query()

We can change "2012-12-21" to whatever date we want to search on. So, next let's make this changeable part of our query into a parameter that gets passed to db_query():

&lt;?php
$query = &lt;&lt;&lt;SQL
SELECT
  n.title, n.nid, COUNT(*) as times
FROM
  {node} n 
JOIN
  {node_view_count} a
ON
    a.nid = n.nid
WHERE
    DATE(from_unixtime(a.timestamp)) = :mydate
GROUP BY
    n.title
ORDER BY times DESC
SQL;


I've put { and } around the table names so that you use this query inside db_query(). By adding :mydate, you can now add in parameter called ':mydate' with a date string to your db_query() call:

$result = db_query($query, array(':mydate' =&gt; '2012-12-21'));


Hopefully, this gets you 90% of the way there.