mysql fetch data between two unix timestamps

Question

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.

You would need the timestamp of each vote. Is that in your db somewhere? — Marty McVry
– Marty McVry, Commented Apr 4, 2013 at 7:18
@MartyMcVry Yes it is. it is stored in another table called rating with timestamps, post id and a flag of the type of vote, i.e. 1 for upvote and 2 for downvote. But i was thinking i should sort it according to the time the post was posted and not the votes. Am i wrong? — coder101
– coder101, Commented Apr 4, 2013 at 7:24
@MartyMcVry Ok, let me rephrase it, i want top content based on the votes(meaning, the most popular ones) in the last 24 hours. now considering this, what do you think i should do? Can u provide some sample code, maybe edit my query if its not too much to ask? — coder101
– coder101, Commented Apr 4, 2013 at 7:40
No, you're right... Didn't read your question through properly... You also wanted the posts to be from within the last 24 hours, so that solves the problem. — Marty McVry
– Marty McVry, Commented Apr 4, 2013 at 7:41
@MartyMcVry Can u see the updated query and tell if i am using it correctly? — coder101
– coder101, Commented Apr 4, 2013 at 9:06

Quassnoi · Accepted Answer · 2013-04-04 07:40:51Z

1

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

edited Apr 4, 2013 at 7:40

answered Apr 4, 2013 at 7:32

Quassnoi

428k94 gold badges628 silver badges624 bronze badges

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

14 Comments

coder101 Over a year ago

NOW() would be server's current time. what if it is located in a different timezone than the user? i would like to use the unix timestamp taken from the user's computer or maybe the ip address. Can you please edit to show how can i use a real timestamp in place of NOW()

Quassnoi Over a year ago

@coder101: taking relativity our of consideration, NOW() would mean "now" regardless of the time zone. But here you are, see the post update.

coder101 Over a year ago

what exactly is ts? I guess it will be a new field created to facilitate sorting based on time and should not be my timestamp field's name?

coder101 Over a year ago

@MartyMcVry and Quassnoi its giving an empty result when i put the timestamp in place of NOW().the query i am using now 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 AND p.unix_timestamp BETWEEN UNIX_TIMESTAMP(1363109802 - INTERVAL 1 DAY) AND UNIX_TIMESTAMP(1363109802)    ORDER BY ci_lower_bound DESC

coder101 Over a year ago

i think instead of function UNIX_TIMESTAMP, FROM_UNIXTIME be used. am i right?

|

Collectives™ on Stack Overflow

mysql fetch data between two unix timestamps

1 Answer 1

14 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Related