MySQL – Formatting Text Field as HH:MM:SS

MySQLquery

I am having to Cast() a Text datatype to a TIME datatype. THe issue I'm running into is when the results display i'm loosing the hh:mm:ss format. The syntax I am using is Select SUM(CAST(salestime As Time)) From testData; How do I display my query results in hh:mm:ss format? I also tried to use this syntax SUM(Cast(Time_Format(salesTime, "%h, %i, %s") As Time)) but that is returning a whole number

EDIT
A few sample of the times in the TEXT column are:

0:05:58
0:00:00
0:31:54
0:23:26

Best Answer

You can use this

Schema (MySQL v8.0)

CREATE TABLE Table1
    (`salesTime` varchar(7))
;
    
INSERT INTO Table1
    (`salesTime`)
VALUES
    ('0:05:58'),
    ('0:00:00'),
    ('0:31:54'),
    ('0:23:26')
;

Query #1

SELECT SEC_TO_TIME( SUM( TIME_TO_SEC(TIME_FORMAT(`salesTime`, "%H:%i:%s"))))  FROM Table1;

| SEC_TO_TIME( SUM( TIME_TO_SEC(TIME_FORMAT(`salesTime`, "%H:%i:%s")))) |
| --------------------------------------------------------------------- |
| 01:01:18                                                              |

View on DB Fiddle

But TIME can only have the maximum of 838:59:59, if your SUM get bigger, you need to convert the seconds also into dd.hh:mm:ss

Related Solutions

Mysql – Multiple left joins or subquery

This is my best shot without seeing any table structures

SELECT
    user_runs.userID,
    user_runs.runID,
    `createdBy`,
    `username`,
    DATE_FORMAT(`dateCreated`,'%d-%m-%Y') `dateCreated`, 
    COUNT(DISTINCT runFavouriteID) AS favourite,
    DATE_FORMAT(MAX(`runDate`),'%d-%m-%Y') AS `lastRan`,
    MIN(`runTime`) AS `fastestTime`
FROM
    (SELECT * FROM `user_runs` WHERE userID=4) user_runs
    INNER JOIN `runs` ON user_runs.runID = runs.runID
    INNER JOIN `users` ON users.userID = runs.createdBy
    LEFT OUTER JOIN `user_run_times` ON user_run_times.runID = user_runs.runID 
    LEFT OUTER JOIN `run_favourites` ON run_favourites.runID = user_runs.runID
GROUP BY
    user_runs.runID,`createdBy`,`username`,`dateCreated`;

You will need these indexes

ALTER TABLE user_runs ADD INDEX userID_runID_ndx (userID,runID);
ALTER TABLE runs      ADD INDEX runID_createdBy_ndx (runID,createdBy);
ALTER TABLE runs      ADD INDEX createdBy_runID_ndx (createdBy,runID);

MySQL Query – Get Content from Last 7 Days

Using DATE_ADD() or DATE_SUB()

SELECT * FROM Table_Name
WHERE connect_time >= DATE_ADD(CURDATE(),INTERVAL -7 DAY);

SELECT * FROM Table_Name
WHERE connect_time >= DATE_SUB(CURDATE(),INTERVAL 7 DAY);

Without those functions, you can also do

SELECT * FROM Table_Name
WHERE connect_time >= (CURDATE() + INTERVAL -7 DAY);

SELECT * FROM Table_Name
WHERE connect_time >= (CURDATE() - INTERVAL 7 DAY);

Best Answer

Related Solutions

Mysql – Multiple left joins or subquery

MySQL Query – Get Content from Last 7 Days

Related Question