MariaDB TIME() Function

In MariaDB, TIME() is a built-in function that extracts the time part from a given time or datetime expression and returns it as a string.

MariaDB TIME() Syntax

This is the syntax of the MariaDB TIME() function:

TIME(expr)

Parameters

expr

Required. A date or datetime expression.

If you supply the wrong number of arguments, MariaDB will report an error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1.

Return value

The MariaDB TIME() function returns the time part of the specified time or datetime.

If the specified expression is not a valid time or datetime, the TIME() function will return NULL.

If the argument is NULL, the TIME() function will return NULL.

MariaDB TIME() Examples

Example 1 - datetime

The following statement shows the basic usage of the MariaDB TIME() function:

SELECT TIME('2023-01-31 10:11:12');

Output:

+-----------------------------+
| TIME('2023-01-31 10:11:12') |
+-----------------------------+
| 10:11:12                    |
+-----------------------------+

Example 2 - time

MariaDB TIME() allows you to extract time from time value:

SELECT TIME('10:11:12');

Output:

+------------------+
| TIME('10:11:12') |
+------------------+
| 10:11:12         |
+------------------+

Example 3 - Fractional Seconds

MariaDB TIME() allows you to extract times from time expressions with fractional seconds:

SELECT
  TIME('2023-01-31 10:11:12.123'),
  TIME('10:11:12.123456');

Output:

+---------------------------------+-------------------------+
| TIME('2023-01-31 10:11:12.123') | TIME('10:11:12.123456') |
+---------------------------------+-------------------------+
| 10:11:12.123                    | 10:11:12.123456         |
+---------------------------------+-------------------------+

Example 4 - Date

If you provide a date expression, MariaDB TIME() will return the first digits as the time:

SELECT TIME('2021-01-01');

Output:

+--------------------+
| TIME('2021-01-01') |
+--------------------+
| 00:20:21           |
+--------------------+

Example 5 - Out of range

MariaDB supports time ranges from '-838:59:59.999999' to '838:59:59.999999', so the maximum return value is '838:59:59.999999'.

SELECT TIME('10:11:12'), TIME('839:11:12');

Output:

+------------------+-------------------+
| TIME('10:11:12') | TIME('839:11:12') |
+------------------+-------------------+
| 10:11:12         | 838:59:59         |
+------------------+-------------------+

In this example , 838:59:59 is returned because 839:11:12 is out of the valid range .

Conclusion

In MariaDB, TIME() is a built-in function that extracts the time part from a given time or datetime expression and returns it as a string.