How the SEC_TO_TIME() function works in Mariadb?

The SEC_TO_TIME() function in MariaDB is used to convert a value representing seconds into a TIME value. This function is useful when you need to display or manipulate time values derived from a duration expressed in seconds.

Syntax

The syntax for the MariaDB SEC_TO_TIME() function is as follows:

SEC_TO_TIME(seconds)

• seconds: A numerical value representing the number of seconds to be converted into a TIME value. It can be a literal value or an expression that evaluates to a number.

The function returns a TIME value in the format 'HH:MM:SS', where HH represents the hour, MM represents the minutes, and SS represents the seconds.

Examples

Example 1: Convert seconds to time format

This example demonstrates how to convert a simple value representing seconds into a TIME value.

SELECT SEC_TO_TIME(3670);

The output for this statement is:

+-------------------+
| SEC_TO_TIME(3670) |
+-------------------+
| 01:01:10          |
+-------------------+

This means that 3670 seconds is equal to 1 hour, 1 minute, and 0 seconds.

Example 2: Convert negative seconds to time format

The SEC_TO_TIME() function can handle negative values as well.

SELECT SEC_TO_TIME(-3670);

The output for this statement is:

+--------------------+
| SEC_TO_TIME(-3670) |
+--------------------+
| -01:01:10          |
+--------------------+

The result shows a negative TIME value representing 1 hour, 1 minute, and 0 seconds before the current time.

Example 3: Convert seconds from a calculation

You can use the SEC_TO_TIME() function with expressions that evaluate to a number of seconds.

DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    work_hours INT
);

INSERT INTO employees (emp_id, emp_name, work_hours) VALUES
    (1, 'John Doe', 28800),
    (2, 'Jane Smith', 32400),
    (3, 'Michael Johnson', 36000);

SELECT emp_name, SEC_TO_TIME(work_hours) AS work_duration
FROM employees;

The output for this statement is:

+-----------------+---------------+
| emp_name        | work_duration |
+-----------------+---------------+
| John Doe        | 08:00:00      |
| Jane Smith      | 09:00:00      |
| Michael Johnson | 10:00:00      |
+-----------------+---------------+

In this example, the work_hours column stores the number of seconds an employee has worked. The SEC_TO_TIME() function converts those values into a readable TIME format.

Example 4: Convert seconds beyond 24 hours

The SEC_TO_TIME() function can handle values representing more than 24 hours.

SELECT SEC_TO_TIME(90000);

The output for this statement is:

+--------------------+
| SEC_TO_TIME(90000) |
+--------------------+
| 25:00:00           |
+--------------------+

This result shows that 90000 seconds is equal to 25 hours.

Example 5: Convert seconds with fractional part

The SEC_TO_TIME() function can handle values with a fractional part, rounding the result to the nearest second.

SELECT SEC_TO_TIME(3670.9);

The output for this statement is:

+---------------------+
| SEC_TO_TIME(3670.9) |
+---------------------+
| 01:01:10.9          |
+---------------------+

In this case, 3670.9 seconds is rounded to 3671 seconds, which is equal to 1 hour, 1 minute, and 11 seconds.

Related Functions

Here are a few functions related to the MariaDB SEC_TO_TIME() function:

• MariaDB TIME_TO_SEC() function is used to convert a TIME value into a number representing the number of seconds.
• MariaDB TIMEDIFF() function is used to calculate the difference between two TIME or DATETIME values and return the result as a TIME value.
• MariaDB MAKETIME() function is used to create a TIME value from the specified hour, minute, and second values.

Conclusion

The SEC_TO_TIME() function in MariaDB is a useful tool for converting values representing seconds into a readable TIME format. It can handle positive and negative values, as well as values representing more than 24 hours. By understanding the syntax and usage of this function, you can effectively manipulate and display time-related data in your MariaDB database.