TIME Datatype in SQL

There are many scenarios in SQL when you need to store the time in the SQL tables of your database.
To store the time in your SQL tables, your first step should be to create a column in your table which is capable of storing the time.
If you want the time to be stored in the column of your table, you need to create a column with the TIME data type.
The TIME data type by default stores the time in "HH:MM:SS" format.
Using the SELECT statement in SQL, you can retrieve the time from the column of the SQL tables.
Along with retrieving the time in the default format in which it is stored, there is a TIME_FORMAT () function in SQL using which the time can be retrieved in a more readable format.
You can retrieve the time in the default format in which it is stored in the table, i.e., "HH:MM:SS," or you also have the option of retrieving the specific parts of the time such as hour, minute, and seconds by choosing an appropriate parameter according to our requirement and passing it to the TIME_FORMAT() function. The time can also be retrieved in a 12 hour and a 24-hour format.
We can also print the time followed by AM/ PM.

Let us see few practical examples to understand this concept more clearly. We will use the MySQL database for writing all the queries.

To create a table in the database, we will first select a database in which we want to create a table.

mysql> USE dbs;

Then we will write the following query to create a table:

mysql> CREATE TABLE items_tbl(ID INT, Item_Name VARCHAR(20), Item_Quantity INT, Item_Price INT, Item_OrderDate DATE, Item_OrderTime TIME);

TIME Datatype in SQL

In the above query, the column named ItemOrderTime will store the time since the datatype of this column is set as a 'TIME'.

Now, we will write a query to insert records in the items_tbl table.

mysql> INSERT INTO items_tbl(ID, Item_Name, Item_Quantity, Item_Price, Item_OrderDate, Item_OrderTime) VALUES(1, "Soap", 5, 200, "2021-07-08", "04:13:52"), (2, "Toothpaste", 2, 80, "2021-07-10", "18:09:01"), (3, "Pen", 10, 50, "2021-07-12", "12:00:02"), (4, "Bottle", 1, 250, "2021-07-13", "15:07:05"), (5, "Brush", 3, 90, "2021-07-15", "19:18:43"), (6, "Notebooks", 10, 1000, "2021-07-26", "05:05:05"), (7, "Handkerchief", 3, 100, "2021-07-28", "10:08:12"), (8, "Chips Packet", 5, 50, "2021-07-30", "08:16:58"), (9, "Marker", 2, 30, "2021-08-13", "02:18:32"), (10, "Scissors", 1, 60, "2021-08-13", "07:17:30");

TIME Datatype in SQL

We will execute the SELECT query to verify that all the records are inserted successfully in the items_tbl table.

mysql> SELECT *FROM items_tbl;

ID	Item_Name	Item_Quantity	Item_Price	Item_OrderDate	Item_OrderTime
1	Soap	5	200	2021-07-08	04:13:52
2	Toothpaste	2	80	2021-07-10	18:09:01
3	Pen	10	50	2021-07-12	12:00:02
4	Bottle	1	250	2021-07-13	15:07:05
5	Brush	3	90	2021-07-15	19:18:43
6	Notebooks	10	1000	2021-07-26	05:05:05
7	Handkerchief	3	100	2021-07-28	10:08:12
8	Chips Packet	5	50	2021-07-30	08:16:58
9	Marker	2	30	2021-08-13	02:18:32
10	Scissors	1	60	2021-08-13	07:17:30

The above query results show that the time is retrieved in the default format in which it is stored, i.e., 'HH:MM:SS'.

Example 1:

Write a query to retrieve all the values from the item_tbl and the order time of items in a 12-hour format along with the AM/ PM to retrieve the time values in a more readable format.

Query:

mysql> SELECT ID, Item_Name, Item_Quantity, Item_Price, Item_OrderDate, TIME_FORMAT (Item_OrderTime, "%r") AS Formatted_Time FROM items_tbl;

Here, the TIME_FORMAT() function is applied on Item_OrderTime with the parameter '%r' to print the time in 12-hour format followed by AM/ PM.

You will get the following table as output:

ID	Item_Name	Item_Quantity	Item_Price	Item_OrderDate	Item_OrderTime
1	Soap	5	200	2021-07-08	04:13:52 AM
2	Toothpaste	2	80	2021-07-10	06:09:01 PM
3	Pen	10	50	2021-07-12	12:00:02 PM
4	Bottle	1	250	2021-07-13	03:07:05 PM
5	Brush	3	90	2021-07-15	07:18:43 PM
6	Notebooks	10	1000	2021-07-26	05:05:05 AM
7	Handkerchief	3	100	2021-07-28	10:08:12 AM
8	Chips Packet	5	50	2021-07-30	08:16:58 AM
9	Marker	2	30	2021-08-13	02:18:32 AM
10	Scissors	1	60	2021-08-13	07:17:30 AM

Example 2:

Write a query to retrieve all the values from the item_tbl and the order time of items in a 12-hour format along with the AM/ PM to retrieve the time values in a more readable format.

Query:

mysql> SELECT ID, Item_Name, Item_Quantity, Item_Price, Item_OrderDate, TIME_FORMAT (Item_OrderTime, "%T %p") AS Formatted_Time FROM items_tbl;

Here, the TIME_FORMAT () function is applied on Item_OrderTime with the parameter '%T %p'. '%T' will print the time in 24-hour format, and '%p' will add the AM/ PM to the 24-hour formatted time.

You will get the following table as output:

ID	Item_Name	Item_Quantity	Item_Price	Item_OrderDate	Item_OrderTime
1	Soap	5	200	2021-07-08	04:13:52 AM
2	Toothpaste	2	80	2021-07-10	18:09:01 PM
3	Pen	10	50	2021-07-12	12:00:02 PM
4	Bottle	1	250	2021-07-13	15:07:05 PM
5	Brush	3	90	2021-07-15	19:18:43 PM
6	Notebooks	10	1000	2021-07-26	05:05:05 AM
7	Handkerchief	3	100	2021-07-28	10:08:12 AM
8	Chips Packet	5	50	2021-07-30	08:16:58 AM
9	Marker	2	30	2021-08-13	02:18:32 AM
10	Scissors	1	60	2021-08-13	07:17:30 AM

Example 3:

Write a query to retrieve all the values from the item_tbl and only the order hour of items from the entire order time in a 24-hour format.

Query:

mysql> SELECT ID, Item_Name, Item_Quantity, Item_Price, Item_OrderDate, TIME_FORMAT (Item_OrderTime, "%H") AS Item_OrderHour FROM items_tbl;

Here, the TIME_FORMAT () function is applied on Item_OrderTime with the parameter '%H' to print specifically the hour at which the item is ordered. Capital H denotes that the hour will be printed in 24-hour format.

You will get the following table as output:

ID	Item_Name	Item_Quantity	Item_Price	Item_OrderDate	Item_OrderHour
1	Soap	5	200	2021-07-08	04
2	Toothpaste	2	80	2021-07-10	18
3	Pen	10	50	2021-07-12	12
4	Bottle	1	250	2021-07-13	15
5	Brush	3	90	2021-07-15	19
6	Notebooks	10	1000	2021-07-26	05
7	Handkerchief	3	100	2021-07-28	10
8	Chips Packet	5	50	2021-07-30	08
9	Marker	2	30	2021-08-13	02
10	Scissors	1	60	2021-08-13	07

Example 4:

Write a query to retrieve all the values from the item_tbl and only the order hour of items from the entire order time in a 12-hour format.

Query:

mysql> SELECT ID, Item_Name, Item_Quantity, Item_Price, Item_OrderDate, TIME_FORMAT (Item_OrderTime, "%h") AS Item_OrderHour FROM items_tbl;

Here, the TIME_FORMAT () function is applied on Item_OrderTime with the parameter '%h' to print specifically the hour at which the item is ordered. Small 'h' denotes that the hour will be printed in a 12-hour format.

You will get the following table as output:

ID	Item_Name	Item_Quantity	Item_Price	Item_OrderDate	Item_OrderHour
1	Soap	5	200	2021-07-08	04
2	Toothpaste	2	80	2021-07-10	6
3	Pen	10	50	2021-07-12	12
4	Bottle	1	250	2021-07-13	03
5	Brush	3	90	2021-07-15	07
6	Notebooks	10	1000	2021-07-26	05
7	Handkerchief	3	100	2021-07-28	10
8	Chips Packet	5	50	2021-07-30	08
9	Marker	2	30	2021-08-13	02
10	Scissors	1	60	2021-08-13	07

Example 5:

Write a query to retrieve all the values from the item_tbl and only the minute at which an item is ordered from the entire order time.

Query:

mysql> SELECT ID, Item_Name, Item_Quantity, Item_Price, Item_OrderDate, TIME_FORMAT (Item_OrderTime, "%i") AS Item_OrderMinute FROM items_tbl;

Here, the TIME_FORMAT () function is applied on Item_OrderTime with the parameter '%i' to print specifically the minute at which the item is ordered. Small 'i' denotes that the minute from the entire 'Item_OrderTime' will be printed.

You will get the following table as output:

ID	Item_Name	Item_Quantity	Item_Price	Item_OrderDate	Item_OrderMinute
1	Soap	5	200	2021-07-08	13
2	Toothpaste	2	80	2021-07-10	09
3	Pen	10	50	2021-07-12	00
4	Bottle	1	250	2021-07-13	07
5	Brush	3	90	2021-07-15	18
6	Notebooks	10	1000	2021-07-26	05
7	Handkerchief	3	100	2021-07-28	08
8	Chips Packet	5	50	2021-07-30	16
9	Marker	2	30	2021-08-13	18
10	Scissors	1	60	2021-08-13	17

Example 6:

Write a query to retrieve all the values from the item_tbl and only the seconds at which an item is ordered from the entire order time.

mysql> SELECT ID, Item_Name, Item_Quantity, Item_Price, Item_OrderDate, TIME_FORMAT (Item_OrderTime, "%S") AS Item_OrderSeconds FROM items_tbl;

Here, the TIME_FORMAT () function is applied on Item_OrderTime with the parameter '%S' to print specifically the seconds at which the item is ordered. Capital 'S' denotes that the minute from the entire 'Item_OrderTime' will be printed.

You will get the following table as output:

ID	Item_Name	Item_Quantity	Item_Price	Item_OrderDate	Item_Order_Seconds
1	Soap	5	200	2021-07-08	52
2	Toothpaste	2	80	2021-07-10	01
3	Pen	10	50	2021-07-12	02
4	Bottle	1	250	2021-07-13	05
5	Brush	3	90	2021-07-15	43
6	Notebooks	10	1000	2021-07-26	05
7	Handkerchief	3	100	2021-07-28	12
8	Chips Packet	5	50	2021-07-30	58
9	Marker	2	30	2021-08-13	32
10	Scissors	1	60	2021-08-13	20

Note: Using Small 's' in the above query will also give the same output.

SQL Tutorial

SQL Database

SQL Table

SQL Select

SQL Clause

SQL Order By

SQL Insert

SQL Update

SQL Delete

SQL Join

SQL Keys

Difference

SQL Injection

SQL String Functions

Miscl

SQL MCQ

PL/SQL Tutorial

Sql Interview Question

SQl Quiz

TIME Datatype in SQL

Comment / Reply From

You May Also Like

SQL COUNT DISTINCT

What is Race Condition

Trigger in SQL

How to Use ORDER BY in SQL

How to Use GROUP BY in SQL

SQL Arithmetic Operators

SQL Languages

How to use Auto-Increment in SQL

Popular Post

PHP Tutorial

NET Framework

Python Tutorial

Language

PHP Tutorial

NET Framework

Python Tutorial

Newsletter