Jaynesh Posted July 12, 2011 Share Posted July 12, 2011 Hello Ive got a table post(post_id, user_id, post, timestamp) when a post is outputted I want it to display how many minutes/hours ago it was posted. How would I do this? I'm assuming it requires math? For e.g This is my post Submitted 2 hours and 32 minutes ago Quote Link to comment Share on other sites More sharing options...
AyKay47 Posted July 12, 2011 Share Posted July 12, 2011 you can insert into your db table the NOW() function. Then compare the difference of the time stored to the current time Quote Link to comment Share on other sites More sharing options...
Jaynesh Posted July 12, 2011 Author Share Posted July 12, 2011 Hi I have already setup a timestamp in my table. How would I compare the difference? This is the format on my timestamp: 0000-00-00 00:00:00 Quote Link to comment Share on other sites More sharing options...
xyph Posted July 12, 2011 Share Posted July 12, 2011 You could get the difference in seconds, and use basic math to go from there. SELECT `content`, `date`, UNIX_TIMESTAMP() - UNIX_TIMESTAMP(`date`) as `difference` FROM `table` Once extracted, `difference` will hold the amount of seconds between now and the post. Divide by 86400, you get days. Divide remainder by 3600, you get hours. Divide that remainder by 60, minutes. The final remainder is seconds. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.