Introduction:

In this blog, we will explore different methods to convert seconds into minutes and hours using MySQL. We'll provide comprehensive explanations, code examples, and compare various approaches for efficient time conversion.

Method 1: Using Mathematical Operations

One straightforward way to convert seconds to minutes and hours in MySQL is by utilizing mathematical operations. We can divide the total seconds by 60 to get the minutes, and further divide by 60 to obtain the hours.

SELECT
    seconds,
    FLOOR(seconds / 3600) AS hours,
    FLOOR((seconds % 3600) / 60) AS minutes
FROM time_data;

Output:

• We use the FLOOR function to round down the division results.
• seconds / 3600 gives us the hours.
• (seconds % 3600) / 60 calculates the remaining seconds after extracting hours and then converts them to minutes.

Method 2: Using TIME_FORMAT Function

MySQL provides the TIME_FORMAT function, which allows formatting time in various ways, including hours and minutes.

SELECT
    seconds,
    TIME_FORMAT(SEC_TO_TIME(seconds), '%H:%i') AS hours_minutes
FROM time_data;

Output:

• SEC_TO_TIME converts seconds to a time value.
• %H represents hours and %i represents minutes in the format string.

Method 3: Using TIMESTAMPADD Function

The TIMESTAMPADD function can be used to add a specified time interval to a given timestamp.

SELECT
    seconds,
    TIMESTAMPADD(SECOND, seconds, '2000-01-01 00:00:00') AS converted_time
FROM time_data;

Output:

• We add the given seconds to a base timestamp of '2000-01-01 00:00:00'.
• The result is the converted timestamp, including hours and minutes.

Method 4: Using User-Defined Functions

For more complex scenarios, consider creating a user-defined function (UDF) to encapsulate the time conversion logic.

DELIMITER //

CREATE FUNCTION ConvertSecondsToTime(seconds INT)
RETURNS VARCHAR(10)
BEGIN
    DECLARE hours INT;
    DECLARE minutes INT;
    SET hours = FLOOR(seconds / 3600);
    SET minutes = FLOOR((seconds % 3600) / 60);
    RETURN CONCAT(hours, ':', LPAD(minutes, 2, '0'));
END;
//

DELIMITER ;

SELECT
    seconds,
    ConvertSecondsToTime(seconds) AS hours_minutes
FROM time_data;

Output:

• We create a user-defined function ConvertSecondsToTime that takes seconds as input and returns a formatted time string.
• The function calculates hours and minutes and concatenates them with a colon.

Conclusion:

In this blog, we explored multiple methods to convert seconds into minutes and hours using MySQL. We covered basic mathematical operations, built-in functions like TIME_FORMAT and TIMESTAMPADD.