title: Rounding time-stamp columns to make them cover a larger period of time
body: <p>So as the title of the question explains, i want to round my table's time-stamp column's up (e.g <code>123456789</code> becomes <code>12345000</code>) so that i can use those rounded time-stamps to sort the rows of my table based on the day (or at least close to a day) of insertion. </p>
  
  <p>So, what would happen in a regular time-stamp based sort is, the records would be sorted based on the second of insertion (since time-stamps are second-based). What i want to happen is, condense those regular time-stamps to ones that cover days instead of a single seconds, so that a sort sorts my records based on a unit of time larger than a second (i want it to be close to one day). 
  <br>
  <br>
  Here is an example table:</p>
  
  <pre><code>---------POSTS---------
  
  ID   TITLE        TIME     LIKES
  1    "hello wor"  12345    12
  2    "abcdef"     13345    32
  3    "yes no"     55531    99921
  4    "hello wor"  23345    25
  5    "fghijklm"   55532    65
  </code></pre>
  
  <p>Now, consider sorting the above table by <code>TIME desc</code>, the post created at <code>55532</code> would come first, then <code>23345</code>, and so on... However, there is one problem, imagine i want to sort the table by <code>TIME DESC, LIKES DESC</code> so that i get a nice ranking of the posts from the ones that are relatively new and well liked by the community, the post created at <code>55532</code> that has only <code>65</code> likes would come before the post created at <code>55531</code> which has <code>99921</code> likes, obviously, this is inconsistent because a second is trivial and should not matter. So to remove this inconsistency, i want to find a way to change the values of the <code>TIME</code> column into ones that cover larger periods of time, not only 1 second, for example if i could turn them into units that covered a day instead of a second, so then, both the posts created at <code>55531</code> and <code>55532</code> would be treated as equal when my <code>TIME</code> sort executes (because they would now have the same values, which would be the rough day they were both created at, instead of the second they were created at), and then the sort gets to the likes, and then <code>55531</code>, which has <code>99921</code> likes, would be sorted first, and then <code>55532</code>, which only has <code>65</code> likes.
  <br><br> </p>
  
  <p>Creating a new column that contains the day a post was created is not an option, i have to do that using the time-stamp column i have.
  <br><br><br>
  So far, i've come up with 2 potential solutions:  </p>
  
  <p>1- Use <code>ROUND(time, -5) as condensed_time</code>, and then use <code>condensed_time</code> to sort the rows.<br>
  <br>2- Uglier solution and not dynamic, <code>case when (UNIX_TIMESTAMP() - time) &lt; 86400 then 0 when (UNIX_TIMESTAMP() - time) &lt; 259200 then 1 when (UNIX_TIMESTAMP() - time) &lt; 604800 then 2 when (UNIX_TIMESTAMP() - time) &lt; 864000 then 3 when (UNIX_TIMESTAMP() - time) &lt; 1728000 then 4 when (UNIX_TIMESTAMP() - time) &lt; 2592000 then 5 else 6 end)</code>.
  <br><br><br>
  Here is the rest of my query in case you are interested in the big picture (i want to use the condensed time i am talking about to sort posts by the day (or 20 hours, or 40, you get the idea) they were created at): </p>
  
  <pre><code>select *, (select count(id) from post_votes where post_votes.post_id = posts.id) as total_votes, (select count(id) from favorites where favorites.post_id = posts.id) as total_favorites, (select count(id) from notifications where type = 5 and notifications.extra = posts.id) as total_sends, ROUND(time, -5) as condensed_post_time from posts where (title like concat('%',:tag ,'%') or title like concat('%', :tag) or title like concat(:tag,'%')) and (posted_by not in (SELECT SUBSTRING_INDEX(user_ids, '-', -1) as blocked_user FROM blocked_users WHERE SUBSTRING_INDEX(user_ids, '-', 1) = :base_user_id) and posted_by not in (SELECT SUBSTRING_INDEX(user_ids, '-', 1) as blocker FROM blocked_users WHERE SUBSTRING_INDEX(user_ids, '-', -1) = :base_user_id) and posted_by not in (SELECT user_id from account_states)) order by condensed_post_time desc, total_votes desc, total_favorites desc limit 3 
  </code></pre>
  
  <p><br><br><br>
  If you have any questions or want me to elaborate more, please inform me in the comments, i will gladly answer, and thanks for taking the time to read my question :)</p>