How the TIME_TO_SEC() function works in Mariadb?

The TIME_TO_SEC() function in MariaDB is used to convert a time value into the corresponding number of seconds.

Posted on

The TIME_TO_SEC() function in MariaDB is used to convert a time value into the corresponding number of seconds. This function takes a string representing the time as its input and returns an integer value representing the total number of seconds in that time.

Syntax

The syntax of the MariaDB TIME_TO_SEC() function is as follows:

TIME_TO_SEC(time)
  • time: This is a required parameter that specifies the time value to be converted into seconds. The time value should be in the format ‘HH:MM:SS’ or ‘HH:MM:SS.fraction’ (e.g., ‘12:45:30.000’).

The function returns an integer value representing the total number of seconds in the given time value.

Examples

Example 1: Converting a simple time value

This example demonstrates how to convert a basic time value into seconds using the TIME_TO_SEC() function.

SELECT TIME_TO_SEC('01:00:00');

The following is the output:

+-------------------------+
| TIME_TO_SEC('01:00:00') |
+-------------------------+
|                    3600 |
+-------------------------+

In this example, the time value ‘01:00:00’ (1 hour) is converted into 3600 seconds.

Example 2: Converting a time value with fractions

This example shows how to convert a time value with fractional seconds using the TIME_TO_SEC() function.

SELECT TIME_TO_SEC('01:00:00.500');

The following is the output:

+-----------------------------+
| TIME_TO_SEC('01:00:00.500') |
+-----------------------------+
|                    3600.500 |
+-----------------------------+

Although the input time value includes fractional seconds (‘01:00:00.500’), the TIME_TO_SEC() function returns seconds part (3600) including the fractional part (.500).

Example 3: Converting negative time values

This example demonstrates how the TIME_TO_SEC() function handles negative time values.

SELECT TIME_TO_SEC('-01:00:00');

The following is the output:

+--------------------------+
| TIME_TO_SEC('-01:00:00') |
+--------------------------+
|                    -3600 |
+--------------------------+

The TIME_TO_SEC() function correctly handles negative time values and returns the corresponding negative number of seconds.

Example 4: Using TIME_TO_SEC() with a table

This example shows how to use the TIME_TO_SEC() function in combination with a table.

DROP TABLE IF EXISTS time_table;
CREATE TABLE time_table (id INT, time_value TIME);
INSERT INTO time_table VALUES (1, '08:30:00'), (2, '12:45:30'), (3, '-02:15:00');

SELECT id, time_value, TIME_TO_SEC(time_value) AS seconds
FROM time_table;

The following is the output:

+------+------------+---------+
| id   | time_value | seconds |
+------+------------+---------+
|    1 | 08:30:00   |   30600 |
|    2 | 12:45:30   |   45930 |
|    3 | -02:15:00  |   -8100 |
+------+------------+---------+

In this example, the TIME_TO_SEC() function is used to convert the time_value column from the time_table into the corresponding number of seconds.

Example 5: Using TIME_TO_SEC() in a calculation

This example demonstrates how to use the TIME_TO_SEC() function in a calculation.

SELECT TIME_TO_SEC('02:30:00') / 60 AS minutes;

The following is the output:

+----------+
| minutes  |
+----------+
| 150.0000 |
+----------+

In this example, the TIME_TO_SEC() function converts the time value ‘02:30:00’ into seconds (9000), and then the result is divided by 60 to get the number of minutes (150.0).

The following are some functions related to the MariaDB TIME_TO_SEC() function:

  • MariaDB SEC_TO_TIME() function is used to convert a number of seconds into a time value.
  • MariaDB TIMEDIFF() function is used to calculate the difference between two time values.
  • MariaDB MAKETIME() function is used to create a time value from the hour, minute, and second values.
  • MariaDB HOUR(), MINUTE(), and SECOND() functions are used to extract the hour, minute, and second parts, respectively, from a time value.

Conclusion

The TIME_TO_SEC() function in MariaDB is a useful tool for converting time values into the corresponding number of seconds. It can be used in various calculations and operations involving time values. By understanding the syntax and usage of this function, along with related functions, developers can effectively manipulate and work with time data in their MariaDB applications.