Mysql FROM_UNIXTIME

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_fro开发者_如何学Pythonm 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.

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?

If your dateline columns in the database is stored as a timestamp, you should convert the PHP variables to timestamp, and not convert the dateline column to datetimes.

Why ?
In order to avoid applying the FROM_UNIXTIME() function to that column : if you do use that function like you posted, MySQL will have to scan the whole table for each request, applying that function to all lines...
... which means you'll loose the advantage of any index you might have on dateline -- and have your MySQL server do more work.

So, rewrite your query the other way arround : convert the DateTimes PHP variables to UNIX timestamps (the datatype you have in your database), using DateTime::format with a U format :

$ts_from = $date_from->format('U');
$ts_to = $date_to->format('U');

$result_pag_num = mysql_query("SELECT COUNT(*) AS count 
                    FROM messages WHERE (
                        folder = '$folder' 
                        AND username = '$username' 
                        AND dateline BETWEEN $ts_from AND $ts_to
                    )");

As a sidenote : maybe you're doing that earlier in your code, but don't forget to properly escape your data -- especially strings, using mysql_real_escape_string().

You don't need the FROM_UNIXTIME function. Since the dateline column is datatype TIMESTAMP, MySQL will do the conversion for you:

AND dateline BETWEEN '2011-03-01' AND '2011-03-11 17:00'

To really investigate the issue, we need to know the actual contents of the $date_from and $date_to variables, and the actual statement being passed to MySQL.

Also, timezone settings may influence how datetime values are interpreted and compared.

If you are passing unixtime style integers, then you should omit the single quotes (to avoid any possibility that MySQL is doing a string comparison)

AND FROM_UNIXTIME(dateline) BETWEEN 1299801600 AND 1298998800