MySQL: update field with “x hours xx minutes”using int from another field We have two fields that hold time data


totalTime (int): number of minutes (eg 243)
totalTimeDesc (string): normal english representation (eg '4 Hours 3 Minutes')


We have a lot of different formats in totalTimeDesc ('5 min', '5m', '5 minutes', etc) and want to calculate a uniform string using the totalTime field and save that in totalTimeDesc.

Can I do this inside mysql, or do I need to write a script to make those changes?  

I haven't written stored procedures before, but this might be a good use case for them.  Here is a snippet of JS that we used to do the calculation/formatting in a similar case:

// get value &amp; calculate time/format
var mins = $(this).val();
var calcMin = mins % 60;
var calcMinText = (calcMin &gt; 1) ? "Minutes" : "Minute";
var calcHrs = Math.floor(mins / 60);
var calcHrsText = (calcHrs &gt; 1) ? "Hours" : "Hour";

// set time/format as calcDesc
var calcDesc = "";  
if(calcHrs &gt; 0) { calcDesc = calcHrs+" "+calcHrsText+" "; }
if(calcMin &gt; 0) { calcDesc += calcMin+" "+calcMinText; }
I have a single MySQL query that do all the heavy lifting

SELECT TimeDisplay FROM
(
    SELECT TRIM(REPLACE(CONCAT(IF(dy=0,'',
    IF(dy=1,'1 day ',CONCAT(dy,' days '))),
    IF(hr=0,'',IF(hr=1,'1 hour ', CONCAT(hr,' hours  '))),
    IF(mn=0,'',IF(mn=1,'1 minute ',CONCAT(mn,' minutes '))),
    IF(sc=0,'',IF(sc=1,'1 second ',CONCAT(sc,' seconds ')))),'  ',' ')) TimeDisplay
    FROM
    (
        SELECT dy,hr,mn,MOD(sec_aaaa,60) sc FROM
        (
            SELECT dy,hr,FLOOR((sec_aaa-dy*86400-hr*3600)/60) mn,sec_aaa sec_aaaa
            FROM
            (
                SELECT dy,FLOOR((sec_aa-(dy*86400))/3600) hr,sec_aa sec_aaa FROM
                (
                    SELECT FLOOR(sec_a/86400) dy,sec_a sec_aa
                    FROM (SELECT 243 sec_a) A
                ) AA
            ) AAA
        ) AAAA
    ) AAAAA
) AAAAAA;


When you cut and paste it, this is what it looks like

mysql&gt; SELECT TimeDisplay FROM
    -&gt; (
    -&gt;     SELECT TRIM(REPLACE(CONCAT(IF(dy=0,'',
    -&gt;     IF(dy=1,'1 day ',CONCAT(dy,' days '))),
    -&gt;     IF(hr=0,'',IF(hr=1,'1 hour ', CONCAT(hr,' hours  '))),
    -&gt;     IF(mn=0,'',IF(mn=1,'1 minute ',CONCAT(mn,' minutes '))),
    -&gt;     IF(sc=0,'',IF(sc=1,'1 second ',CONCAT(sc,' seconds ')))),'  ',' ')) TimeDisplay
    -&gt;     FROM
    -&gt;     (
    -&gt;         SELECT dy,hr,mn,MOD(sec_aaaa,60) sc FROM
    -&gt;         (
    -&gt;             SELECT dy,hr,FLOOR((sec_aaa-dy*86400-hr*3600)/60) mn,sec_aaa sec_aaaa
    -&gt;             FROM
    -&gt;             (
    -&gt;                 SELECT dy,FLOOR((sec_aa-(dy*86400))/3600) hr,sec_aa sec_aaa FROM
    -&gt;                 (
    -&gt;                     SELECT FLOOR(sec_a/86400) dy,sec_a sec_aa
    -&gt;                     FROM (SELECT 243 sec_a) A
    -&gt;                 ) AA
    -&gt;             ) AAA
    -&gt;         ) AAAA
    -&gt;     ) AAAAA
    -&gt; ) AAAAAA;
+---------------------+
| TimeDisplay         |
+---------------------+
| 4 minutes 3 seconds |
+---------------------+
1 row in set (0.00 sec)

mysql&gt;


The key to using this query is this section

FROM (SELECT 243 sec_a) A


Simply replace the 243 with totalTime and you are off and running.

Your Original Question

Would you like to see a very wicked UPDATE JOIN to fix all totalTimeDesc fields?

Sample Data

use test
drop table if exists mytimes;
create table mytimes
(
    id int not null auto_increment primary key,
    totalTime int not null,
    totalTimeDesc varchar(128)
);
insert into mytimes (totalTime) values (243),(2430),(24300),(243000);
SELECT * FROM mytimes;


Proposed UPDATE JOIN

UPDATE mytimes J INNER JOIN 
(
    SELECT id,TRIM(REPLACE(CONCAT(IF(dy=0,'',
    IF(dy=1,'1 day ',CONCAT(dy,' days '))),
    IF(hr=0,'',IF(hr=1,'1 hour ', CONCAT(hr,' hours  '))),
    IF(mn=0,'',IF(mn=1,'1 minute ',CONCAT(mn,' minutes '))),
    IF(sc=0,'',IF(sc=1,'1 second ',CONCAT(sc,' seconds ')))),'  ',' ')) TimeDisplay
    FROM
    (
        SELECT id,dy,hr,mn,MOD(sec_aaaa,60) sc FROM
        (
            SELECT id,dy,hr,FLOOR((sec_aaa-dy*86400-hr*3600)/60) mn,sec_aaa sec_aaaa
            FROM
            (
                SELECT id,dy,FLOOR((sec_aa-(dy*86400))/3600) hr,sec_aa sec_aaa FROM
                (
                    SELECT id,FLOOR(sec_a/86400) dy,sec_a sec_aa
                    FROM (SELECT id,totalTime sec_a FROM mytimes) A
                ) AA
            ) AAA
        ) AAAA
    ) AAAAA
) K USING (id)
SET J.totalTimeDesc = K.TimeDisplay;


If you look carefully, I slipped in 

FROM (SELECT id,totalTime sec_a FROM mytimes) A


in place of

FROM (SELECT 243 sec_a) A


and propagated the id column through subqueries

Sample Data Loaded

mysql&gt; use test
Database changed
mysql&gt; drop table if exists mytimes;
Query OK, 0 rows affected (0.01 sec)

mysql&gt; create table mytimes
    -&gt; (
    -&gt;     id int not null auto_increment primary key,
    -&gt;     totalTime int not null,
    -&gt;     totalTimeDesc varchar(128)
    -&gt; );
Query OK, 0 rows affected (0.02 sec)

mysql&gt; insert into mytimes (totalTime) values (243),(2430),(24300),(243000);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql&gt; SELECT * FROM mytimes;
+----+-----------+---------------+
| id | totalTime | totalTimeDesc |
+----+-----------+---------------+
|  1 |       243 | NULL          |
|  2 |      2430 | NULL          |
|  3 |     24300 | NULL          |
|  4 |    243000 | NULL          |
+----+-----------+---------------+
4 rows in set (0.00 sec)

mysql&gt;


Proposed UPDATE JOIN Executed

mysql&gt; UPDATE mytimes J INNER JOIN
    -&gt; (
    -&gt;     SELECT id,TRIM(REPLACE(CONCAT(IF(dy=0,'',
    -&gt;     IF(dy=1,'1 day ',CONCAT(dy,' days '))),
    -&gt;     IF(hr=0,'',IF(hr=1,'1 hour ', CONCAT(hr,' hours  '))),
    -&gt;     IF(mn=0,'',IF(mn=1,'1 minute ',CONCAT(mn,' minutes '))),
    -&gt;     IF(sc=0,'',IF(sc=1,'1 second ',CONCAT(sc,' seconds ')))),'  ',' ')) TimeDisplay
    -&gt;     FROM
    -&gt;     (
    -&gt;         SELECT id,dy,hr,mn,MOD(sec_aaaa,60) sc FROM
    -&gt;         (
    -&gt;             SELECT id,dy,hr,FLOOR((sec_aaa-dy*86400-hr*3600)/60) mn,sec_aaa sec_aaaa
    -&gt;             FROM
    -&gt;             (
    -&gt;                 SELECT id,dy,FLOOR((sec_aa-(dy*86400))/3600) hr,sec_aa sec_aaa FROM
    -&gt;                 (
    -&gt;                     SELECT id,FLOOR(sec_a/86400) dy,sec_a sec_aa
    -&gt;                     FROM (SELECT id,totalTime sec_a FROM mytimes) A
    -&gt;                 ) AA
    -&gt;             ) AAA
    -&gt;         ) AAAA
    -&gt;     ) AAAAA
    -&gt; ) K USING (id)
    -&gt; SET J.totalTimeDesc = K.TimeDisplay;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql&gt; SELECT * FROM mytimes;
+----+-----------+----------------------------+
| id | totalTime | totalTimeDesc              |
+----+-----------+----------------------------+
|  1 |       243 | 4 minutes 3 seconds        |
|  2 |      2430 | 40 minutes 30 seconds      |
|  3 |     24300 | 6 hours 45 minutes         |
|  4 |    243000 | 2 days 19 hours 30 minutes |
+----+-----------+----------------------------+
4 rows in set (0.00 sec)

mysql&gt;


UPDATE 2014-07-23 16:20 EDT

I just realized, your units are in minutes not seconds

Here is the quick fix

UPDATE mytimes J INNER JOIN 
(
    SELECT id,TRIM(REPLACE(CONCAT(IF(dy=0,'',
    IF(dy=1,'1 day ',CONCAT(dy,' days '))),
    IF(hr=0,'',IF(hr=1,'1 hour ', CONCAT(hr,' hours  '))),
    IF(mn=0,'',IF(mn=1,'1 minute ',CONCAT(mn,' minutes '))),
    IF(sc=0,'',IF(sc=1,'1 second ',CONCAT(sc,' seconds ')))),'  ',' ')) TimeDisplay
    FROM
    (
        SELECT id,dy,hr,mn,MOD(sec_aaaa,60) sc FROM
        (
            SELECT id,dy,hr,FLOOR((sec_aaa-dy*86400-hr*3600)/60) mn,sec_aaa sec_aaaa
            FROM
            (
                SELECT id,dy,FLOOR((sec_aa-(dy*86400))/3600) hr,sec_aa sec_aaa FROM
                (
                    SELECT id,FLOOR(sec_a/86400) dy,sec_a sec_aa
                    FROM (SELECT id,totalTime*60 sec_a FROM mytimes) A
                ) AA
            ) AAA
        ) AAAA
    ) AAAAA
) K USING (id)
SET J.totalTimeDesc = K.TimeDisplay;
SELECT * FROM mytimes;


I changed

FROM (SELECT id,totalTime sec_a FROM mytimes) A


to 

FROM (SELECT id,totalTime*60 sec_a FROM mytimes) A


If you rerun the UPDATE JOIN, you get this

mysql&gt; UPDATE mytimes J INNER JOIN
    -&gt; (
    -&gt;     SELECT id,TRIM(REPLACE(CONCAT(IF(dy=0,'',
    -&gt;     IF(dy=1,'1 day ',CONCAT(dy,' days '))),
    -&gt;     IF(hr=0,'',IF(hr=1,'1 hour ', CONCAT(hr,' hours  '))),
    -&gt;     IF(mn=0,'',IF(mn=1,'1 minute ',CONCAT(mn,' minutes '))),
    -&gt;     IF(sc=0,'',IF(sc=1,'1 second ',CONCAT(sc,' seconds ')))),'  ',' ')) TimeDisplay
    -&gt;     FROM
    -&gt;     (
    -&gt;         SELECT id,dy,hr,mn,MOD(sec_aaaa,60) sc FROM
    -&gt;         (
    -&gt;             SELECT id,dy,hr,FLOOR((sec_aaa-dy*86400-hr*3600)/60) mn,sec_aaa sec_aaaa
    -&gt;             FROM
    -&gt;             (
    -&gt;                 SELECT id,dy,FLOOR((sec_aa-(dy*86400))/3600) hr,sec_aa sec_aaa FROM
    -&gt;                 (
    -&gt;                     SELECT id,FLOOR(sec_a/86400) dy,sec_a sec_aa
    -&gt;                     FROM (SELECT id,totalTime*60 sec_a FROM mytimes) A
    -&gt;                 ) AA
    -&gt;             ) AAA
    -&gt;         ) AAAA
    -&gt;     ) AAAAA
    -&gt; ) K USING (id)
    -&gt; SET J.totalTimeDesc = K.TimeDisplay;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 4  Changed: 0  Warnings: 0

mysql&gt; SELECT * FROM mytimes;
+----+-----------+---------------------------+
| id | totalTime | totalTimeDesc             |
+----+-----------+---------------------------+
|  1 |       243 | 4 hours 3 minutes         |
|  2 |      2430 | 1 day 16 hours 30 minutes |
|  3 |     24300 | 16 days 21 hours          |
|  4 |    243000 | 168 days 18 hours         |
+----+-----------+---------------------------+
4 rows in set (0.00 sec)

mysql&gt;


GIVE IT A TRY !!!