SQL + Drupal: how to filter correctly using SQL within a Drupal Database?

Question

I have been using Drupal for quite sometime, but now I have come to a crossroad: using MySQL to extract data from my site's database via phpMyAdmin.

Everything was going great with the basics (I was going through w3school's tutorials), I learned a lot about SELECT, operators, booleans, etc., but then I came to a stop while trying to filter my results concerning dates, with UNIX TIMESTAMP.

I need to be able to filter results between two dates and, no matter what I do or try, my results are wrong.

I have been trying to wrap my head around this concept (which I believe I already have) but I haven't been able to filter correctly using timestamps.

I went back to square 1 to review syntax, so I want to think that's not my problem, but I definitely need help.

Has anyone found something in the web that might cover this issue? Everytime I look for UNIX TIMESTAMP articles, they are quite superficial (I guess this is not a very hard issue after all).

Thanks in advance.

Answer 1

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.

Answer 2

I use this page to convert the dates and then use it in my query. Hope this works for you.

Answer 3

I came up with something like this:

SELECT SUBSTR(field_actu_fecha_value, 1, 10), COUNT(*) 
FROM content_type_actualidad
WHERE  `field_actu_fecha_value` >= '2011-05-21'
AND  `field_actu_fecha_value` <= '2011-06-01'
GROUP BY SUBSTR(field_actu_fecha_value, 1, 10)

I learned a lot about "DATE TIME" and how Drupal fortunately uses ISO time (so you don't have to deal with UNIXTIME, although it does not hurt to know about this too), so you can break the "DATE TIME" to search and refine your search as specifically as you need to.

For example, here I am filtering all "content_type_actualidad" nodes from the very last week of May 2011, and counting those results too.