MySQL MariaDB

How to extract the time part with MySQL TIME function

MySQL is a popular RDBMS used to manage the data of websites in tables; it offers a lot of functions to get specific results efficiently. Functions in MySQL provide ease in managing data in the databases. TIME() is one of those functions. In this post, we will discuss the TIME() function and its working in MySQL using different examples.

What is a TIME() in MySQL

This function is used to extract the time part from the data provided to it, to understand it, we will consider some examples, but before proceeding to the examples, let us discuss the general syntax of using TIME():

TIME(expression);

The explanation to the syntax is very simple, just write the function and inside the function write the expression or column name from where you want to extract the time, for example, we will extract time from the expression “2021-11-15 14:25:10” using the function of TIME():

SELECT TIME(2021-11-15 14:25:10);

In the above command, the time is extracted from the expression which is containing both date and time, similarly, to extract the time from the present time and date, use the command:

Similarly, we can also extract time from the results of passing the NOW() function:

We can also extract time from the data of a table, for this, let us create a table of employees timesheets using the command:

CREATE TABLE emp_timesheet (emp_id INT, emp_name VARCHAR(50), emp_time_rec TIMESTAMP);

We have used the “TIMESTAMP” datatype to define emp_time_rec, because in this column we want to insert date and time records, to insert data in the table, execute the command:

INSERT INTO emp_timesheet VALUES (1, ‘John’,2021-11-01 08:07:23),(2, ‘Paul’,2021-11-01 08:02:00),(3, ‘Sophia’,2021-11-01 09:17:03),(4, ‘Anna’,2021-11-01 07:48:23);

To display the table, run the command:

SELECT * FROM emp_timesheet;

To extract the time only from the “emp_time_rec” column, use the command:

SELECT emp_id, emp_name, emp_time_rec, TIME(emp_time_rec) FROM emp_timesheet;

From the above output, we can see that the function TIME() extracted the time from the data of the column of “emp_time_rec” and displayed it separately.

Conclusion

MySQL is the popular RDBMS used to manage the data of the websites and applications; it supports a lot of functions that take single or multiple inputs and returns one specific output for a particular task. In this post, we have discussed the TIME() function that is used to extract the time from the given data. This function can be applied on a single input or a number of inputs of the table by using its data of the column of the table.

About the author

Hammad Zahid

I'm an Engineering graduate and my passion for IT has brought me to Linux. Now here I'm learning and sharing my knowledge with the world.