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.