Problem with the function UNIX_TIMESTAMP

Question

Query 1

SELECT SessionInfo.IVRSessionInfoID
FROM   SessionInfo
WHERE  SessionCallTime BETWEEN UNIX_TIMESTAMP('2013-08-01 00:00:00') 
                           AND UNIX_TIMESTAMP('2013-08-01 23:59:59')
ORDER  BY SessionInfo.SessionCallTime DESC;

Query 2

SELECT SessionInfo.IVRSessionInfoID
FROM   SessionInfo
WHERE  ( SessionInfo.SessionCallTime BETWEEN '2013-08-01 00:00:00' 
                                         AND '2013-08-01 23:59:59' )
ORDER  BY SessionInfo.SessionCallTime DESC; 

What is the difference? Why does the first query give 0 rows and the second give records?

In this table there are 20000 rows between these two dates.

Answer 1

My guess:

From: http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_unix-timestamp

UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)

If called with no argument, returns a Unix timestamp (seconds since '1970-01-01 00:00:00' UTC) as an unsigned integer. If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' UTC

In the first query, you're comparing your date to the number of seconds 2013-08-01 00:00:00 and 2013-08-01 23:59:00 are after 1970-01-01, meaning you're comparing your dates to seconds.

In the second query, you're actually comparing date to other dates.