MySQL: update field with “x hours xx minutes”using int from another field

Question

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 & calculate time/format
var mins = $(this).val();
var calcMin = mins % 60;
var calcMinText = (calcMin > 1) ? "Minutes" : "Minute";
var calcHrs = Math.floor(mins / 60);
var calcHrsText = (calcHrs > 1) ? "Hours" : "Hour";

// set time/format as calcDesc
var calcDesc = "";  
if(calcHrs > 0) { calcDesc = calcHrs+" "+calcHrsText+" "; }
if(calcMin > 0) { calcDesc += calcMin+" "+calcMinText; }

RolandoMySQLDBA · Accepted Answer · 2014-07-23 20:20:52Z

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> 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;
+---------------------+
| TimeDisplay         |
+---------------------+
| 4 minutes 3 seconds |
+---------------------+
1 row in set (0.00 sec)

mysql>

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> use test
Database changed
mysql> drop table if exists mytimes;
Query OK, 0 rows affected (0.01 sec)

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

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

mysql> 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>

Proposed UPDATE JOIN Executed

mysql> 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;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> 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>

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> 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;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 4  Changed: 0  Warnings: 0

mysql> 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>

GIVE IT A TRY !!!

Cool! Except I'm getting Error : Unknown column 'totalTime' in 'field list'. Does that innermost SELECT need to select from mytable too? — doub1ejack, Commented Jul 23, 2014 at 20:04
Oh, also there are no seconds involved in this case; totalTime is in minutes. But I (mostly) see what you're doing. — doub1ejack, Commented Jul 23, 2014 at 20:06
Oh I'm sorry, you are expressing minutes not seconds. Let me fix that... — RolandoMySQLDBA, Commented Jul 23, 2014 at 20:17
I updated the query to perform everything in minutes instead of seconds — RolandoMySQLDBA, Commented Jul 23, 2014 at 20:21
This is really great. Does the trick nicely, thank you. The important lesson for me here is 1) the nested select statements that generate calculated data, and 2) good use of IF() statements. I'll try to roll these into my repertoire. — doub1ejack, Commented Jul 23, 2014 at 20:39

Stack Exchange Network

MySQL: update field with “x hours xx minutes”using int from another field

1 Answer 1

Your Original Question

Sample Data

Proposed UPDATE JOIN

Sample Data Loaded

Proposed UPDATE JOIN Executed

UPDATE 2014-07-23 16:20 EDT

GIVE IT A TRY !!!

Your Answer

Not the answer you're looking for? Browse other questions tagged
mysql
stored-procedures
or ask your own question.

Hot Network Questions

MySQL: update field with “x hours xx minutes”using int from another field

1 Answer 1

Your Original Question

Sample Data

Proposed UPDATE JOIN

Sample Data Loaded

Proposed UPDATE JOIN Executed

UPDATE 2014-07-23 16:20 EDT

GIVE IT A TRY !!!

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged mysqlstored-procedures or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
mysql
stored-procedures
or ask your own question.