- 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); 
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"); 
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.
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.
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 |
Leave a Reply