In MariaDB, TIME() is a built-in date and time function that extracts the time part from a given time or datetime expression and returns it as a string.
It accepts one argument, which is the time or datetime value for which you want to extract the time.
Syntax
The syntax goes like this:
TIME(expr)Where expr is the time or datetime expression for which to extract the time.
Example
Here’s an example to demonstrate:
SELECT TIME('2030-02-01 10:30:45');Result:
+-----------------------------+
| TIME('2030-02-01 10:30:45') |
+-----------------------------+
| 10:30:45 |
+-----------------------------+
Time Values
Here’s an example that extracts the time from a time value:
SELECT TIME('10:30:45');Result:
+------------------+
| TIME('10:30:45') |
+------------------+
| 10:30:45 |
+------------------+
Microseconds
Here’s an example that includes microseconds:
SELECT TIME('2030-02-01 10:30:45.123456');Result:
+------------------------------------+
| TIME('2030-02-01 10:30:45.123456') |
+------------------------------------+
| 10:30:45.123456 |
+------------------------------------+
Larger Hours
TIME values can be in the range '-838:59:59.999999' to '838:59:59.999999'.
Therefore, the hour portion can be much higher than 23:
SELECT TIME('578:30:45');Result:
+-------------------+
| TIME('578:30:45') |
+-------------------+
| 578:30:45 |
+-------------------+
Negative Times
Negative times are valid:
Example
SELECT TIME('-578:30:45');Result:
+--------------------+
| TIME('-578:30:45') |
+--------------------+
| -578:30:45 |
+--------------------+
Out of Range Hours
Time values outside the range '-838:59:59.999999' to '838:59:59.999999' are capped at the relevant boundary, and include a warning.
Example:
SELECT TIME('978:30:45');Result (using vertical output):
+-------------------+
| TIME('978:30:45') |
+-------------------+
| 838:59:59 |
+-------------------+
1 row in set, 1 warning (0.003 sec)
Let’s check the warning:
SHOW WARNINGS;Result (using vertical output):
+---------+------+---------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------+ | Warning | 1292 | Truncated incorrect time value: '978:30:45' | +---------+------+---------------------------------------------+
Current Date
We can pass NOW() as the datetime argument to use the current time:
SELECT
NOW(),
TIME(NOW());Result:
+---------------------+-------------+ | NOW() | TIME(NOW()) | +---------------------+-------------+ | 2021-05-27 10:24:23 | 10:24:23 | +---------------------+-------------+
Invalid Arguments
When passed an invalid argument, TIME() returns null with a warning:
SELECT TIME('Ten Thirty AM');Result:
+-----------------------+
| TIME('Ten Thirty AM') |
+-----------------------+
| NULL |
+-----------------------+
1 row in set, 1 warning (0.002 sec)
Check the warning:
SHOW WARNINGS;Result:
+---------+------+---------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------+ | Warning | 1292 | Incorrect time value: 'Ten Thirty AM' | +---------+------+---------------------------------------+
Missing Argument
Calling TIME() with the wrong number of arguments, or without passing any arguments, results in an error:
SELECT TIME();Result:
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
And another example:
SELECT TIME('10:30:45', '06:30:45');Result:
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 ' '06:30:45')' at line 1