MySQL: How to convert seconds to mm:ss format?

ONLINECAST

2 min read

·

07-10-2024

5

0

Share

MySQL: Converting Seconds to mm:ss Format

Have you ever needed to display a duration in minutes and seconds in your MySQL database? Perhaps you're working with audio or video files, tracking time spent on tasks, or calculating elapsed time. While MySQL stores durations in seconds, presenting them in a more human-readable format like mm:ss (minutes:seconds) can greatly enhance the user experience.

This article will guide you through different methods to convert seconds to mm:ss format in MySQL. Let's dive in!

Understanding the Problem

Imagine you have a table named events with a column duration storing the duration of each event in seconds. You want to display this duration in the format mm:ss when retrieving data from the database.

CREATE TABLE events (
  id INT PRIMARY KEY AUTO_INCREMENT,
  event_name VARCHAR(255),
  duration INT
);

INSERT INTO events (event_name, duration) VALUES 
  ('Concert', 6000), 
  ('Webinar', 3600),
  ('Movie', 7200);

Now, let's explore different solutions to convert the duration column from seconds to mm:ss.

Method 1: Using SEC_TO_TIME() Function

The SEC_TO_TIME() function is a built-in MySQL function that converts a number of seconds into a time value. This time value can then be formatted using the TIME_FORMAT() function.

SELECT 
    event_name,
    TIME_FORMAT(SEC_TO_TIME(duration), '%i:%s') AS formatted_duration
FROM 
    events;

This query:

1. Selects the event_name and uses the SEC_TO_TIME() function to convert the duration in seconds to a time value.
2. The TIME_FORMAT() function then formats the resulting time value in the desired mm:ss format using the format specifier %i:%s.

Method 2: Using FLOOR() and MOD() Functions

If you prefer a more manual approach, you can utilize the FLOOR() and MOD() functions to calculate minutes and seconds directly:

SELECT 
    event_name,
    FLOOR(duration / 60) AS minutes,
    MOD(duration, 60) AS seconds,
    CONCAT(LPAD(FLOOR(duration / 60), 2, '0'), ':', LPAD(MOD(duration, 60), 2, '0')) AS formatted_duration
FROM 
    events;

Here's the breakdown:

1. FLOOR(duration / 60) calculates the number of minutes.
2. MOD(duration, 60) calculates the remaining seconds.
3. CONCAT() combines minutes and seconds, adding leading zeros using LPAD() for consistent formatting.

Conclusion

Both methods effectively convert seconds to mm:ss format in MySQL. Choose the method that best suits your specific needs and coding style.

Remember, always test your queries with sample data to ensure they function correctly before deploying them in production. Happy coding!