SQL + Drupal: how to filter correctly using SQL within a Drupal Database?
I'll make an attempt but I haven't quite identified the problem yet. Does your result set deviate from the expected? Is there an error? Answers to those could make it easier to assist.

A Unix timestamp is simply the number of seconds since Jan 1st, 1970.

The query

SELECT type FROM node
    WHERE TYPE = 'page'
    AND FROM_UNIXTIME(created, '%Y') = 2011
    AND FROM_UNIXTIME(created, '%m') = 07;


works for me; it returns all nodes of type 'page' created in month 7 of year 2011. These queries also work:

SELECT * FROM node -- all pages from July
    WHERE TYPE = 'page'
    AND FROM_UNIXTIME(created, '%Y-%m') = '2011-07';

SELECT * FROM node -- all pages from May, June, July
    WHERE TYPE = 'page'
    AND FROM_UNIXTIME(created, '%Y-%m') BETWEEN '2011-05' AND '2011-07';

SELECT * FROM node -- all pages from weeks 33, 34, 35, 36, starting on Mondays
    WHERE TYPE = 'page'
    AND FROM_UNIXTIME(created, '%u') BETWEEN 33 AND 36;

SELECT * FROM node -- all pages created between 8 morning and 4 afternoon
    WHERE TYPE = 'page'
    AND FROM_UNIXTIME(created, '%H') BETWEEN 8 AND 16;


However, you can also use PHP to generate a Unix timestamp from a date, then simply compare that with the stored values (following is untested):

$t1 = mktime(0, 0, 0, 5, 1, 2011);
$t2 = mktime(23, 59, 59, 7, 31, 2011);
//$t2 = mktime(0, 0, -1, 8, 1, 2011); // 1 second before midnight Jul 31/Aug 1st.
SELECT * FROM node -- all pages from May, June, and July
    WHERE TYPE = 'page'
    AND created BETWEEN $t1 AND $t2;


I would suggest you take an exact query you're trying to use and execute it directly against the database in order to determine if the problem is with the query or something else. If it is the query, play around in there until you get the desired result or ask for help.

NB: Drupal expects tables to be enclosed in braces, in case it needs to handle table prefixes. This means the first query (in Drupal) should look like this:

SELECT type FROM {node}
    WHERE TYPE = 'page'
    AND FROM_UNIXTIME(created, '%Y') = 2011
    AND FROM_UNIXTIME(created, '%m') = 07;


If you don't use table prefixes it's not necessary (that is, it should still work) but it's good practice to always include them regardless.