MySQL - TIME() Function

---

The DATE, DATETIME and TIMESTAMP datatypes in MySQL are used to store the date, date and time, time stamp values respectively. Where a time stamp is a numerical value representing the number of milliseconds from '1970-01-01 00:00:01' UTC (epoch) to the specified time. MySQL provides a set of functions to manipulate these values.

The MYSQL TIME() function is used to retrieve and return the time part of the given date time or time expression and returns the result in the form o f a string.

Syntax

Following is the syntax of the above function –

TIME(expr);

Where, expr is the date-time or the time expression from which you need to extract the time.

Example 1

Following example demonstrates the usage of the TIME() function –

mysql> SELECT TIME('00:00:00 09:40:45.2300');
+--------------------------------+
| TIME('00:00:00 09:40:45.2300') |
+--------------------------------+
| 09:40:45.2300                  |
+--------------------------------+
1 row in set (0.00 sec)

Example 2

Following is another example of this function –

mysql> SELECT TIME('00 12:38:48');
+---------------------+
| TIME('00 12:38:48') |
+---------------------+
| 12:38:48            |
+---------------------+
1 row in set (0.00 sec)

Example 3

We can also pass the date-time expression as an argument to this function –

mysql> SELECT TIME('2015-09-05 09:40:45.2300');
+----------------------------------+
| TIME('2015-09-05 09:40:45.2300') |
+----------------------------------+
| 09:40:45.2300                    |
+----------------------------------+
1 row in set (0.00 sec)

Example 4

In the following example we are retrieving the time value from the current timestamp —

mysql> SELECT TIME(CURRENT_TIMESTAMP);
+-------------------------+
| TIME(CURRENT_TIMESTAMP) |
+-------------------------+
| 16:29:04                |
+-------------------------+
1 row in set (0.00 sec)

Example 5

We can pass the result of the NOW() function as an argument to this function –

mysql> SELECT TIME(NOW());
+-------------+
| TIME(NOW()) |
+-------------+
| 16:29:35    |
+-------------+
1 row in set (0.00 sec)

Example 6

Suppose we have created a table named dispatches_data with 5 records in it using the following queries –

mysql> CREATE TABLE dispatches_data(
	ProductName VARCHAR(255),
	CustomerName VARCHAR(255),
	DispatchTimeStamp timestamp,
	Price INT,
	Location VARCHAR(255)
);
insert into dispatches_data values('Key-Board', 'Raja', TIMESTAMP('2019-05-04', '15:02:45'), 7000, 'Hyderabad');
insert into dispatches_data values('Earphones', 'Roja', TIMESTAMP('2019-06-26', '14:13:12'), 2000, 'Vishakhapatnam');
insert into dispatches_data values('Mouse', 'Puja', TIMESTAMP('2019-12-07', '07:50:37'), 3000, 'Vijayawada');
insert into dispatches_data values('Mobile', 'Vanaja' , TIMESTAMP ('2018-03-21', '16:00:45'), 9000, 'Chennai');
insert into dispatches_data values('Headset', 'Jalaja' , TIMESTAMP('2018-12-30', '10:49:27'), 6000, 'Goa');

Following query retrieves the time values from the DispatchTimeStamp column —

mysql> SELECT ProductName, CustomerName, DispatchTimeStamp, Price, TIME(DispatchTimeStamp) FROM dispatches_data;
+-------------+--------------+---------------------+-------+-------------------------+
| ProductName | CustomerName | DispatchTimeStamp   | Price | TIME(DispatchTimeStamp) |
+-------------+--------------+---------------------+-------+-------------------------+
| Key-Board   | Raja         | 2019-05-04 15:02:45 | 7000  | 15:02:45                |
| Earphones   | Roja         | 2019-06-26 14:13:12 | 2000  | 14:13:12                |
| Mouse       | Puja         | 2019-12-07 07:50:37 | 3000  | 07:50:37                |
| Mobile      | Vanaja       | 2018-03-21 16:00:45 | 9000  | 16:00:45                |
| Headset     | Jalaja       | 2018-12-30 10:49:27 | 6000  | 10:49:27                |
+-------------+--------------+---------------------+-------+-------------------------+
5 rows in set (0.25 sec)

Example 7

Suppose we have created a table named SubscriberDetails with 5 records in it using the following queries –

mysql> CREATE TABLE SubscriberDetails (
	SubscriberName VARCHAR(255),
	PackageName VARCHAR(255),
	SubscriptionTimeStamp timestamp
);
insert into SubscriberDetails values('Raja', 'Premium', TimeStamp('2020-10-21 20:53:49'));
insert into SubscriberDetails values('Roja', 'Basic', TimeStamp('2020-11-26 10:13:19'));
insert into SubscriberDetails values('Puja', 'Moderate', TimeStamp('2021-03-07 05:43:20'));
insert into SubscriberDetails values('Vanaja', 'Basic', TimeStamp('2021-02-21 16:36:39'));
insert into SubscriberDetails values('Jalaja', 'Premium', TimeStamp('2021-01-30 12:45:45'));

Following query retrieves and displays the subscription time for all the records —

mysql> SELECT SubscriberName, PackageName, SubscriptionTimeStamp, TIME(SubscriptionTimeStamp) FROM SubscriberDetails;
+----------------+-------------+-----------------------+-----------------------------+
| SubscriberName | PackageName | SubscriptionTimeStamp | TIME(SubscriptionTimeStamp) |
+----------------+-------------+-----------------------+-----------------------------+
| Raja           | Premium     | 2020-10-21 20:53:49   | 20:53:49                    |
| Roja           | Basic       | 2020-11-26 10:13:19   | 10:13:19                    |
| Puja           | Moderate    | 2021-03-07 05:43:20   | 05:43:20                    |
| Vanaja         | Basic       | 2021-02-21 16:36:39   | 16:36:39                    |
| Jalaja         | Premium     | 2021-01-30 12:45:45   | 12:45:45                    |
+----------------+-------------+-----------------------+-----------------------------+
5 rows in set (0.06 sec)