CODEWITHSUNDEEP
sqltimestamphive
arsenal
arsenal

Reputation: 24194

Difference between Timestamp is 15 minutes

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

Upvotes: 1

Views: 908

Answers (1)

RobIII
RobIII

Reputation: 8831

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

Upvotes: 2

Related Questions