CODEWITHSUNDEEP
phpjquerymysql
user4380792
user4380792

Reputation:

Calculate hours and minutes from milliseconds on MySQL table

I have a MySQL table with 3 columns: Name; Month; Milliseconds. I want to calculate the hours and minutes times a certain value of 2.5 for example.

SET @miliseconds = 36000000000;
SET @s = @miliseconds / 1000; 
SET @hours = FLOOR(@s / 3600);
SET @minutes = FLOOR(@s / 60 - @hours * 60); 
SET @seconds = ROUND(@s - @hours * 3600 - @minutes * 60);

SELECT @hours, @minutes, @seconds;

This gives the correct hours and minutes upon the givens value, not according the Milliseconds column. Plus, it doesn't let me select the Name and Month columns. How can I do a msqli query in a php file which gives me the math value of milliseconds on my table upon the criteria of Name, Month, Milliseconds.

My goal is to display it in a html table. I know how can I do it in jQuery, but I don't know how can I serialize it. To serialize it in the table I use the following code:

var tr = data
            for (var i = 0; i < data.length; i++) {
                var tr = $('<tr/>');

$(tr).append("<td>" + data[i].mes + "</td>");
$(tr).append("<td>" + data[i].mills + "</td>");

$('.tableMesA').append(tr);
}

But is there a way where I can calculate the first column of "mes" with the value of converting milliseconds to a math value?

Upvotes: 0

Views: 5420

Answers (1)

Shadow
Shadow

Reputation: 34232

You just have to repeat the above calculations while you select from the table:

select t.*,floor(millisecond / 3600000) as hours, floor(millisecond / 60000) - floor(millisecond / 3600000) * 60 as minutes
from t

You can also use the sec_to_time() function as well:

select t.*, sec_to_time(floor(milliseconds/1000)) as time
from t

Upvotes: 2

Related Questions