CODEWITHSUNDEEP
mysqlsql
Curtis Crewe
Curtis Crewe

Reputation: 4336

mySQL query returning 0 results using UNIX_TIMESTAMP

I have the following query which I'm assuming should return results :

SELECT 
    * 
FROM 
    media
WHERE 
    sent < package
AND 
    flag = 0
AND 
    UNIX_TIMESTAMP(last_run) + 1800 < UNIX_TIMESTAMP(NOW())
ORDER BY 
    last_run ASC 
LIMIT 1

I have the following row inside my DB

`last_run` = '2014-09-13 17:30:0'
`flag` = '0'
`sent` = '4'
`package` = '400'

As now it's currently 2014-09-15 02:53:57 as per the server with a time() of 1410749659 I assume this should be returned? if not, what's the reason behind this?

When checking the timestamps against mySQL I'm getting the following result from this query

SELECT UNIX_TIMESTAMP(last_run), UNIX_TIMESTAMP(NOW()), last_run FROM media LIMIT 1
----------------------------------------------------------------------------
UNIX_TIMESTAMP(last_run)      UNIX_TIMESTAMP(NOW())      last_run
1410752462                    1410750296                 2014-09-14 20:41:02

Upvotes: 0

Views: 358

Answers (2)

spencer7593
spencer7593

Reputation: 108370

Q: I assume this [row] should be returned?

A: Yes, we'd expect the row you described to be returned (assuming that there's not implicit data conversions going, e.g. the datatype of the sent and package columns are integer.

With last_run column of datatype DATETIME, we'd expect this predicate:

UNIX_TIMESTAMP(last_run) + 1800 < UNIX_TIMESTAMP(NOW())

would be equivalent to:

last_run < NOW() - INTERVAL 1800 SECOND

The most likely explanation is that there is no row in the table that satisfies all of the predicates; the other predicates should also be investigated. (Test with those other predicates removed.)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Why are you using UNIX_TIMESTAMP. Assuming last_run is datetime, just do:

SELECT m.* 
FROM media m
WHERE sent < package AND flag = 0 AND
      last_run < now() - interval 30 minute
ORDER BY last_run ASC 
LIMIT 1;

Upvotes: 1

Related Questions