How the TIME-function() function works in Mariadb?

The TIME() function in MariaDB is used to extract the time portion from a datetime or time expression.

Posted on

The TIME() function in MariaDB is used to extract the time portion from a datetime or time expression. It returns the time part of the input expression as a string in the format ‘HH:MM:SS’ or ‘HH:MM:SS.fraction’, depending on the input and whether fractional seconds are included or not.

Syntax

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

TIME(expr)
  • expr: This is the required expression from which the time part needs to be extracted. It can be a TIME, DATETIME, or a string that can be parsed as a valid time or datetime value.

The function returns a string representing the time portion of the input expression in the format ‘HH:MM:SS’ or ‘HH:MM:SS.fraction’.

Examples

Example 1: Extracting the time portion from a datetime value

This example demonstrates how to extract the time portion from a datetime value using the TIME() function.

SELECT TIME('2023-05-01 10:30:00');

The following is the output:

+-----------------------------+
| TIME('2023-05-01 10:30:00') |
+-----------------------------+
| 10:30:00                    |
+-----------------------------+

In this example, the TIME() function extracts the time portion ‘10:30:00’ from the given datetime value ‘2023-05-01 10:30:00’.

Example 2: Extracting the time portion from a time value

This example shows how to extract the time portion from a time value using the TIME() function.

SELECT TIME('10:30:00');

The following is the output:

+------------------+
| TIME('10:30:00') |
+------------------+
| 10:30:00         |
+------------------+

In this example, the TIME() function simply returns the input time value ‘10:30:00’ as it is already a time value.

Example 3: Extracting the time portion from a string

This example demonstrates how to extract the time portion from a string using the TIME() function.

SELECT TIME('2023-05-01 14:45:30');

The following is the output:

+-----------------------------+
| TIME('2023-05-01 14:45:30') |
+-----------------------------+
| 14:45:30                    |
+-----------------------------+

In this example, the TIME() function extracts the time portion ‘14:45:30’ from the given string ‘2023-05-01 14:45:30’.

Example 4: Using TIME() with a table

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

DROP TABLE IF EXISTS events;
CREATE TABLE events (
    event_name VARCHAR(255),
    event_datetime DATETIME
);
INSERT INTO events VALUES
    ('Meeting', '2023-05-01 09:00:00'),
    ('Presentation', '2023-05-01 13:30:00');

SELECT event_name, TIME(event_datetime) AS event_time
FROM events;

The following is the output:

+--------------+------------+
| event_name   | event_time |
+--------------+------------+
| Meeting      | 09:00:00   |
| Presentation | 13:30:00   |
+--------------+------------+

In this example, the TIME() function is used to extract the time portion from the event_datetime column in the events table, and it is included in the result set along with the event_name.

Example 5: Using TIME() with DATE_FORMAT()

This example demonstrates how to use the TIME() function in combination with the DATE_FORMAT() function to format the output.

SELECT DATE_FORMAT(TIME('14:30:00'), '%h:%i %p') AS formatted_time;

The following is the output:

+----------------+
| formatted_time |
+----------------+
| 02:30 PM       |
+----------------+

In this example, the DATE_FORMAT() function is used to format the time value returned by the TIME() function into a 12-hour format with AM/PM notation.

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

  • MariaDB DATE() function is used to extract the date portion from a datetime expression.
  • MariaDB NOW() function is used to retrieve the current date and time.
  • MariaDB CURTIME() function is similar to TIME(), but it returns the time portion of the current date and time as a TIME value instead of a string.
  • MariaDB TIMEDIFF() function is used to calculate the difference between two time or date-time expressions.

Conclusion

The TIME() function in MariaDB is a useful tool for extracting the time portion from datetime or time expressions. It can be used in various scenarios, such as data manipulation, formatting, and calculations involving time values. By understanding the usage and capabilities of this function, along with related functions, developers can effectively work with time-related data in their MariaDB applications.