How the TIME_FORMAT() function works in Mariadb?

The TIME_FORMAT() function in MariaDB is used to format a time value according to a specified pattern.

Posted on

The TIME_FORMAT() function in MariaDB is used to format a time value according to a specified pattern. It provides a flexible way to customize the display of time values, making it easier to present time data in a desired format.

Syntax

The syntax for the MariaDB TIME_FORMAT() function is as follows:

TIME_FORMAT(time, format)
  • time: The time value to be formatted. This can be a TIME, DATETIME, or TIMESTAMP value.
  • format: A string specifying the output format for the time value. The format string uses specific characters to represent different time components, such as hours, minutes, and seconds.

The TIME_FORMAT() function returns a STRING value representing the formatted time.

Examples

Example 1: Formatting a time value

This example demonstrates how to use the TIME_FORMAT() function to format a time value.

SELECT TIME_FORMAT('18:30:00', '%H:%i:%s');

The following is the output:

+-------------------------------------+
| TIME_FORMAT('18:30:00', '%H:%i:%s') |
+-------------------------------------+
| 18:30:00                            |
+-------------------------------------+

In this example, the TIME_FORMAT() function takes the time value '18:30:00' and formats it according to the pattern '%H:%i:%s', which represents hours, minutes, and seconds, respectively. The result is the same time value, as the specified format matches the input format.

Example 2: Formatting a time value with AM/PM notation

This example shows how to use the TIME_FORMAT() function to format a time value with AM/PM notation.

SELECT TIME_FORMAT('14:45:00', '%h:%i:%s %p');

The following is the output:

+----------------------------------------+
| TIME_FORMAT('14:45:00', '%h:%i:%s %p') |
+----------------------------------------+
| 02:45:00 PM                            |
+----------------------------------------+

In this example, the format string '%h:%i:%s %p' is used, where %h represents the hour in 12-hour format, %i represents the minutes, %s represents the seconds, and %p represents the AM/PM indicator. The time value '14:45:00' (which is in 24-hour format) is converted to '02:45:00 PM'.

Example 3: Formatting a DATETIME value

This example demonstrates how to use the TIME_FORMAT() function to format a DATETIME value.

DROP TABLE IF EXISTS logs;
CREATE TABLE logs (
    id INT PRIMARY KEY,
    event VARCHAR(100),
    event_time DATETIME
);

INSERT INTO logs (id, event, event_time) VALUES
    (1, 'User logged in', '2023-03-09 09:15:30'),
    (2, 'File uploaded', '2023-03-09 14:25:45');

SELECT id, event, TIME_FORMAT(event_time, '%h:%i %p') AS formatted_time
FROM logs;

The following is the output:

+----+----------------+----------------+
| id | event          | formatted_time |
+----+----------------+----------------+
|  1 | User logged in | 09:15 AM       |
|  2 | File uploaded  | 02:25 PM       |
+----+----------------+----------------+

In this example, a table logs is created with a DATETIME column event_time. The TIME_FORMAT() function is used in the SELECT statement to format the event_time column using the pattern '%h:%i %p', which displays the time in 12-hour format with AM/PM notation.

Example 4: Formatting a time value with leading zeros

This example shows how to use the TIME_FORMAT() function to format a time value with leading zeros for single-digit hours and minutes.

SELECT TIME_FORMAT('5:8:15', '%H:%i:%s');

The following is the output:

+-----------------------------------+
| TIME_FORMAT('5:8:15', '%H:%i:%s') |
+-----------------------------------+
| 05:08:15                          |
+-----------------------------------+

In this example, the TIME_FORMAT() function formats the time value '5:8:15' using the pattern '%H:%i:%s'. The %H format specifier ensures that the hour is displayed with a leading zero for single-digit hours, and the %i format specifier ensures that the minutes are displayed with a leading zero for single-digit minutes.

Example 5: Formatting a time value with custom separators

This example demonstrates how to use the TIME_FORMAT() function to format a time value with custom separators between hours, minutes, and seconds.

SELECT TIME_FORMAT('12:30:45', '%H.%i.%s');

The following is the output:

+-------------------------------------+
| TIME_FORMAT('12:30:45', '%H.%i.%s') |
+-------------------------------------+
| 12.30.45                            |
+-------------------------------------+

In this example, the TIME_FORMAT() function formats the time value '12:30:45' using the pattern '%H.%i.%s', where the . character is used as a separator between hours, minutes, and seconds.

The following are a few functions related to the MariaDB TIME_FORMAT() function:

  • The DATE_FORMAT() function is used to format a date value according to a specified pattern.
  • The TIMESTAMP() function retrieves the current date and time as a TIMESTAMP value.
  • The TIME() function extracts the time portion from a DATETIME or TIMESTAMP value.
  • The TIMEDIFF() function calculates the difference between two time values.
  • The MAKETIME() function creates a TIME value from the specified hour, minute, and second components.

Conclusion

The TIME_FORMAT() function in MariaDB is a powerful tool for formatting time values according to a specified pattern. It provides a flexible way to customize the display of time data, allowing developers to present time information in a format that meets their specific requirements. By understanding the syntax and usage of this function, along with related date and time functions like DATE_FORMAT() and TIMESTAMP(), developers can effectively manipulate and present date and time data within their MariaDB applications.