MySQL TIME_FORMAT() Function

Summary: in this tutorial, you will learn how to use the MySQL TIME_FORMAT() function to format time values.

Introduction to MySQL TIME_FORMAT() function

The TIME_FORMAT() function allows you to format a time value according to a specific format.

Here’s the syntax of the TIME_FORMAT() function:

TIME_FORMAT(time, format)Code language: SQL (Structured Query Language) (sql)

In this syntax:

time: The time value that you want to format.
format: The format string that determines how you want to format the time value.

The TIME_FORMAT() returns a string representing the formatted time according to the format string.

If the time or format is NULL, the TIME_FORMAT() function returns NULL.

Since the TIME_FORMAT() only formats time data, the format string contains format specifiers only for hours, minutes, seconds, and microseconds.

The TIME_FORMAT() function is useful for presenting time data in various formats to make it suitable for your applications.

Time format specifiers

The following shows a list of some common format specifiers that you can use in the TIME_FORMAT() function:

%H: Hour (00-23)
%h: Hour (01-12)
%i: Minutes (00-59)
%s: Seconds (00-59)
%p: AM or PM

MySQL TIME_FORMAT() function examples

Let’s take some examples of using the TIME_FORMAT() function.

1) Simple TIMEFORMAT() function example

The following example uses the TIME_FORMAT() function to format the time literal value '15:30:45':

SELECT TIME_FORMAT('15:30:45', '%h:%i %p');Code language: SQL (Structured Query Language) (sql)

Output:

+-------------------------------------+
| TIME_FORMAT('15:30:45', '%h:%i %p') |
+-------------------------------------+
| 03:30 PM                            |
+-------------------------------------+
1 row in set (0.00 sec)Code language: plaintext (plaintext)

2) Using the TIME_FORMAT() function with table data

First, create a new table called appointments with the following structure:

CREATE TABLE appointments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    time TIME NOT NULL,
    description VARCHAR(255) NOT NULL
);Code language: SQL (Structured Query Language) (sql)

The appointments table has three columns:

id: An auto-incremented primary key.
time: A column to store the time of the appointment.
description: A column to store a description of the appointment.

Second, insert some rows into the appointments table:

INSERT INTO appointments (time, description) 
VALUES
    ('10:00:00', 'Meeting with client'),
    ('14:30:00', 'Team discussion'),
    ('16:45:00', 'Project presentation'),
    ('18:15:00', 'Dinner with colleagues');Code language: SQL (Structured Query Language) (sql)

Third, use the TIME_FORMAT() function to format the time data of the appointments table:

SELECT 
  id, 
  description, 
  TIME_FORMAT(time, '%h:%i %p') AS time 
FROM 
  appointments;Code language: SQL (Structured Query Language) (sql)

Output:

+----+------------------------+----------+
| id | description            | time     |
+----+------------------------+----------+
|  1 | Meeting with client    | 10:00 AM |
|  2 | Team discussion        | 02:30 PM |
|  3 | Project presentation   | 04:45 PM |
|  4 | Dinner with colleagues | 06:15 PM |
+----+------------------------+----------+
4 rows in set (0.00 sec)Code language: plaintext (plaintext)

In this example, we select data from the appointments table and use the TIME_FORMAT() function to format the time column into a more readable format.

Summary

Use the TIME_FORMAT() function to format a time value according to a format.

Was this tutorial helpful?