[SOLVED] Unix time show 1h with empty value

Issue

I want to show the summary of all values as visual date. For example:

Here, val1, val2 and val3 are in seconds. In SQL, they are saved as BIGINT(20).

Those values represents the time for each users doing something different. So, the user with key 1 stay 125 seconds on the page which is named val1, 545 seconds on page val2 and 0 on val3.

The objective is to know which page is the more used, where users stay lot of time. So, I want to make a readable time and convert "3600 seconds" into "1h" for example.

More exactly, I want to show as something like this:

I tried to used something like this but it’s only for sql-server.

Actually, I made this:

SELECT
FROM_UNIXTIME(SUM(val1), '%dd %hh %imin') as val1,
FROM_UNIXTIME(SUM(val2), '%dd %hh %imin') as val2,
FROM_UNIXTIME(SUM(val3), '%dd %hh %imin') as val3,
FROM my_table;

But, this is my result:

Even if empty values (such as "0min") don’t disturb me, the 1d 1h is the issue.

How can I made them as 0h or remove them is there are empty ?

Solution

You are getting the 1d because the unix epoch is 1st Jan 1970 so even if you have a var containing zero, the day represented by zero seconds is the 1st if you were outputting the month you would be getting January and the year, you guessed it 1900

This begs the question, what are you actually trying to achieve with this code, my guess is this is not going to do it.

This may be better for your requirement

SELECT CONCAT(
            FLOOR(TIME_FORMAT(SEC_TO_TIME(SUM(val1)), '%H') / 24), 'days ',
            MOD(TIME_FORMAT(SEC_TO_TIME(SUM(val1)), '%H'), 24), 'h:',
            TIME_FORMAT(SEC_TO_TIME(SUM(val1)), '%im:%ss')
        ) AS Val1,
        CONCAT(
            FLOOR(TIME_FORMAT(SEC_TO_TIME(SUM(val2)), '%H') / 24), 'days ',
            MOD(TIME_FORMAT(SEC_TO_TIME(SUM(val2)), '%H'), 24), 'h:',
            TIME_FORMAT(SEC_TO_TIME(SUM(val2)), '%im:%ss')
        ) AS Val2,
        CONCAT(
            FLOOR(TIME_FORMAT(SEC_TO_TIME(SUM(val3)), '%H') / 24), 'days ',
            MOD(TIME_FORMAT(SEC_TO_TIME(SUM(val3)), '%H'), 24), 'h:',
            TIME_FORMAT(SEC_TO_TIME(SUM(val3), '%im:%ss')
        ) AS Val3

RESULTS

'0days 0h:02m:05s', '0days 0h:16m:52s', '0days 0h:00m:00s'

Answered By – RiggsFolly

Answer Checked By – Marilyn (BugsFixing Volunteer)