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