CODEWITHSUNDEEP
mysqlbetween
coder101
coder101

Reputation: 1605

mysql fetch data between two unix timestamps

I want to use the following query with Wilson Score Confidence as taken from how to not sort by average rating to calculate the relevant content based on votes in the last 24 hours from the time user searches. I have unix timestamps stored in a field but its not datetime type.

Now the query is: 

 SELECT p.id, p.post, p.upvotes, p.downvotes, ((upvotes + 1.9208) / (upvotes + downvotes) - 
 1.96 * SQRT((upvotes * downvotes) / (upvotes + downvotes) + 0.9604) /
         (upvotes + downvotes)) / (1 + 3.8416 / (upvotes + downvotes)) 
   AS ci_lower_bound FROM posts p WHERE upvotes + downvotes > 0 
   ORDER BY ci_lower_bound DESC;

I need to fetch top content posted in the last 24 hours. I know i need to put an additional WHERE condition using BETWEEN but not sure how?

Please help.

UPDATE Based on the answer below and some tweaking i made something like this:

 SELECT p.id, p.post, p.upvotes, p.downvotes, ((upvotes + 1.9208) / (upvotes + downvotes) - 1.96 * SQRT((upvotes * downvotes)
 / (upvotes + downvotes) + 0.9604) / (upvotes + downvotes))
 / (1 + 3.8416 / (upvotes + downvotes)) AS ci_lower_bound 
 FROM posts p WHERE upvotes + downvotes > 0 
 AND p.unix_timestamp BETWEEN 1363023402 AND 1363109802 ORDER BY ci_lower_bound DESC

The first value i calculated by subtracting 86400 seconds(i.e. 24 hours) from the current timestamp and got results. Please, if you think if its still can be improved, suggest me.

Upvotes: 0

Views: 900

Answers (1)

Quassnoi
Quassnoi

Reputation: 425301

SELECT  *, complex_formula
FROM    posts
WHERE   upvotes + downvotes > 0
        AND ts BETWEEN UNIX_TIMESTAMP($user_time - INTERVAL 1 DAY) AND UNIX_TIMESTAMP($user_time)

Upvotes: 1

Related Questions