Sort timestamp results just by time?

Asked

Viewed 152 times

-1

I am trying to make a time ranking system of a game. The game uses UNIX time as a measure of time. I need to put the minor times first in ascending order, but as UNIX time includes date as well, time is calculated also by date. Thus, the most recent records end up getting ahead of the rankings due to their date.

Is there any way to sort the search results only by time, discarding the date?

My darlings in Mysql were the following:

$sql ="SELECT * FROM challenge, guild, mapnames WHERE challenge.GuildID = guild.guildid AND challenge.MapID = mapnames.mapid ORDER BY challenge.RecordTime ASC";

and then, when searching on the internet, that way. Still unsuccessful.

$sql ="SELECT * FROM challenge, guild, mapnames WHERE challenge.GuildID = guild.guildid AND challenge.MapID = mapnames.mapid ORDER BY date ASC";

In PHP, to show the result, I used date as a means to format UNIX time for human time:

"<td>", date("H:i:s", $row['RecordTime']) . "</td>",
php mysql timestamp unix

  • It’s hard to understand your problem. If timestamps are of different days wouldn’t it be mandatory to also consider days? Or maybe you’re not referring to the length of the game?

  • I’d like to consider just the time, without the days. The results of timestamps go to a column of a table in mysql and are powered by a third-party application (I’m trying to build a WOW server) so I can’t change that. When a Challenge is finished, the duration time is recorded in timestamp, the lowest times must appear at the top of the ranking.

  • Time is a punctiform vector and not a scalar. You have to have two dates to measure the size of a time vector, the initial measurement date and the final measurement date. The module of subtraction between the final and initial dates of the time measurement is equivalent to the time spent by an event that this yes is a comparable scalar. As time passes in a linear way to the future normally the module calculation is disregarded and only subtraction is made between the temporal measurements.

1 answer

1

Based on the proposed problem, I believe it can be solved with the following functions::

DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP()),'%H:%i:%s')

Upshot:

$sql ="SELECT * FROM challenge, guild, mapnames WHERE challenge.GuildID = guild.guildid AND challenge.MapID = mapnames.mapid ORDER BY DATE_FORMAT(FROM_UNIXTIME(challenge.RecordTime),'%H:%i:%s') ASC";

References:

Browser other questions tagged php mysql timestamp unix

You are not signed in. Login or sign up in order to post.