Difference between Timestamp is 15 minutes

Question

This is the CREATED_TIME 2012-07-17 00:00:22 and this is the Corresponding Timestamp 1342508427000. Here timestamp is 5 seconds more than the CREATED_TIME. I need to see below scenario

Currently I have a query, in which I am joining on created_time and timestamp like this-

ON (UNIX_TIMESTAMP(testingtable1.created_time) = (prod_and_ts.timestamps / 1000))

So in above case, it will not match as timestamp is 5 seconds more than created_time. But I need if the difference between either of the two is within 15 minutes then I need to match it.

So I wrote the below JOIN query- I am not sure whether this is the right way to do it or not?

ON ((UNIX_TIMESTAMP(testingtable1.created_time) - (prod_and_ts.timestamps / 1000)) / 60* 1000 <= 15)

How I can do the above case if difference between timestamps is within 15 minutes then data will get matched by the above ON clause

Yes . . . I would put the 60 on the other side (xxx/1000 > 15*60) just because I think that is a bit clearer. — Gordon Linoff
Why you have greater than sign >. It should be less than sign < right? — arsenal

RobIII RobIII · Accepted Answer · 2012-07-18T23:59:18

I'd prefer the (specifically designed for this purpose!) date and time functions instead of doing al these kinds of calculations with timestamps. You wouldn't believe how much trouble this can cause. Make sure you read and understand this and this

Difference between Timestamp is 15 minutes

1 Answers