Mysql FROM_UNIXTIME

Question

I have 2 PHP variables formatted for DATETIME, but the column is in UNIX timestamp. How do I correctly do a BETWEEN on them?

$date_from and $date_to are in DATETIME format. dateline is stored as a UNIX timestamp.

The following isn't working:

if($date_from != "" && $date_to != "") {
    $result_pag_num = mysql_query("SELECT COUNT(*) AS count FROM messages WHERE (folder = '$folder' AND username = '$username' AND FROM_UNIXTIME(dateline) BETWEEN '$date_from' AND '$date_to')");
} else {
    $result_pag_num = mysql_query("SELECT COUNT(*) AS count FROM messages WHERE (folder = '$folder' AND username = '$username')");
}

Edit: all answers were good. I accepted the person who replied the fasted. The error was my own fault.

Answer 1

You may want to change the query to

... AND dateline BETWEEN UNIX_TIMESTAMP('$date_from') AND UNIX_TIMESTAMP('$date_to')

This way MySQL can use an index on the dateline field. You version forced it to convert every dateline field to a datetime value before doing the comparison, making index usage impossible.

Otherwise, the query looks fine. Are you sure the $date_from and $date_to are properly formatted, and there's records that fit all the clauses in the query?