CODEWITHSUNDEEP
mysql
John
John

Reputation: 1751

MySQL Convert From Seconds To Another Custom Format

I have this javascript code that works fine:

function timeup(s) {
    var d, h, m, s;
    m = Math.floor(s / 60);
    s = s % 60;
    h = Math.floor(m / 60);
    m = m % 60;
    d = Math.floor(h / 24);
    h = h % 24;
    m = m > 9 ? m : "0"+m;
    h = h > 9 ? h : "0"+h;
    s = s > 9 ? s : "0"+s;

    if (d > 0) {
        d = d+" days ";
    } else {
        d = "";
    }
    return d+h+":"+m+":"+s;
}

SO i need same function but in MySQL(because i do SQL query and don't want to use javascript conversion on client side)

So i need to convert in MySQL seconds to get this same output:

timeup(600000) => 6 days 22:40:00
timeup(60000)  => 16:40:00
timeup(6000)   => 01:40:00
timeup(600)    => 00:10:00
timeup(60)     => 00:01:00
timeup(60)     => 00:01:00
timeup(6)      => 00:00:06

So if seconds below day show HH:MM:SS if seconds greater that day show X days HH:MM:SS

I im trying using CONCAT & TIMESTAMPDIFF but i think maybe it should go if then to compare day below 24h or grater to show custom string X days...any help welcome.

Upvotes: 0

Views: 411

Answers (2)

John
John

Reputation: 1751

I now have another problem with this above function that works only on seconds..but i forget to ask in first question that i have in database stored number:

uptime => 1507977507423

And i need to get seconds and show above format from NOW() time

So for example if i have uptime in database so formula will be: NOW() - uptime, i try using this but i get strange output like 34 days 838:59:59 and that is not correct:

SELECT
CONCAT(LPAD(FLOOR(HOUR(SEC_TO_TIME(UNIX_TIMESTAMP(NOW())-SUBSTRING(uptime, 1, length(uptime) - 2))) / 24), 2, 0), ' days ',TIME_FORMAT(SEC_TO_TIME(UNIX_TIMESTAMP(NOW())-SUBSTRING(uptime, 1, length(uptime) - 2) % (24 * 3600)), '%H:%i:%s')) AS nice_date
FROM streams
WHERE id=1;

I get this:

+-------------------+
| nice_date         |
+-------------------+
| 34 days 838:59:59 |
+-------------------+

Upvotes: 0

cardamom
cardamom

Reputation: 7441

I tested this and it seems to do the job:

DROP FUNCTION  IF EXISTS GET_HOUR_MINUTES;
DELIMITER $$
CREATE FUNCTION GET_HOUR_MINUTES(seconds INT) 
  RETURNS VARCHAR(16) 
BEGIN
  RETURN CONCAT(LPAD(FLOOR(HOUR(SEC_TO_TIME(seconds)) / 24), 2, 0), ' days ',TIME_FORMAT(SEC_TO_TIME(seconds % (24 * 3600)), '%H:%i:%s'));
END; 
$$
DELIMITER ;

Test it like this:

SELECT GET_HOUR_MINUTES(600001);

That returns

'06 days 22:40:01'

It seems to want, at least in MySQL Workbench, to have the database you are using selected before you run it. It saves the function within the database, that is, you can see it in the column on the left with Tables, Views, Stored Procedures and Functions.

Upvotes: 1

Related Questions