mySQL query returning 0 results using UNIX_TIMESTAMP

Question

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

On a performance note... performing the addition operation on the column prevents MySQL from using an index range scan operation. We typically prefer to use a bare column on one side, and do any required addition/subtraction operations on the constant side. e.g. last_run < NOW() - INTERVAL 1800 SECOND. But that aside, what is the datatype of the last_run column? — spencer7593
– spencer7593, Commented Sep 15, 2014 at 3:04
Why does last_run have a timestamp greater than now()? Is this a timezone issue? — Gordon Linoff
– Gordon Linoff, Commented Sep 15, 2014 at 3:08
From those timestamps, last_run is 2014-09-14 22:41:02. NOW() is 2014-09-14 22:04:56 — Barmar
– Barmar, Commented Sep 15, 2014 at 3:08

spencer7593 · Accepted Answer · 2014-09-15 04:43:24Z

1

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.)

answered Sep 15, 2014 at 4:43

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2014-09-15 02:57:45Z

1

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;

answered Sep 15, 2014 at 2:57

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

3 Comments

Barmar Over a year ago

If his original query doesn't work, why do you think this will work any better?

Gordon Linoff Over a year ago

@Barmar . . . It just seemed like converting both sides could have an effect on the comparison.

spencer7593 Over a year ago

This is a "better" query, in that it may give better performacne if a suitable index is defined. This will allow MySQL to make use a suitable index for both the predicate and the ORDER BY. But this also doesn't really answer the question that was asked. If the OP query isn't returning a row, then we wouldn't expect this query to return a row. (Personally, I'd use Gordon's query, this is the one that I would debug, not the query with the unnecessary UNIX_TIMESTAMP functions. (It's been confirmed in a comment on the question that the datatype of last_run column is DATETIME.)

Collectives™ on Stack Overflow

mySQL query returning 0 results using UNIX_TIMESTAMP

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related