2

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.

2
  • 3
    What is the datatype of SessionCallTime? – ypercubeᵀᴹ Oct 1 '13 at 12:53
  • timestamp................. – karukala.sr Oct 1 '13 at 13:30
5

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.