MySQL - CURRENT_TIME() Function, CURRENT_TIME

---

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 CURRENT_TIME() function is the synonym for CURTIME(). It used to get the current time. The resultant value is a string or a numerical value based on the context and, the time returned will be in the 'hh:mm:ss' or hhmmss format.

Syntax

Following is the syntax of the above function –

CURRENT_TIME();

Example 1

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

mysql> SELECT CURRENT_TIME();
+----------------+
| CURRENT_TIME() |
+----------------+
|       20:53:49 |
+----------------+
1 row in set (0.07 sec)

Example 2

Following is an example of this function in numerical context –

mysql> SELECT CURRENT_TIME() +0;
+-------------------+
| CURRENT_TIME() +0 |
+-------------------+
|            205404 |
+-------------------+
1 row in set (0.00 sec)

Example 3

You can add seconds to the current time as shown below –

mysql> SELECT CURRENT_TIME()+12;
+-------------------+
| CURRENT_TIME()+12 |
+-------------------+
|            205432 |
+-------------------+
1 row in set (0.00 sec)

Example 4

We can also subtract the desired number of seconds from the current time using this function –

mysql> SELECT CURRENT_TIME()-22213;
+----------------------+
| CURRENT_TIME()-22213 |
+----------------------+
|               183388 |
+----------------------+
1 row in set (0.00 sec)

Example 5

You can use CURRENT_TIME instead of CURRENT_TIME() to retrieve the current time.

mysql> SELECT CURRENT_TIME;
+--------------+
| CURRENT_TIME |
+--------------+
|     20:57:46 |
+--------------+
1 row in set (0.00 sec)
mysql> SELECT CURRENT_TIME+0;
+----------------+
| CURRENT_TIME+0 |
+----------------+
|         205759 |
+----------------+
1 row in set (0.00 sec)

Example 6

Let us create another table with name Sales in MySQL database using CREATE statement as follows –

mysql> CREATE TABLE sales(
	ID INT,
	ProductName VARCHAR(255),
	CustomerName VARCHAR(255),
	DispatchDate date,
	DispatchTime time,
	Price INT,
	Location VARCHAR(255)
);
Query OK, 0 rows affected (2.22 sec)

Now, we will insert 5 records in Sales table using INSERT statements −

insert into sales values (1, 'Key-Board', 'Raja', DATE('2019-09-01'), TIME('11:00:00'), 7000, 'Hyderabad');
insert into sales values (2, 'Earphones', 'Roja', DATE('2019-05-01'), TIME('11:00:00'), 2000, 'Vishakhapatnam');
insert into sales values (3, 'Mouse', 'Puja', DATE('2019-03-01'), TIME('10:59:59'), 3000, 'Vijayawada');
insert into sales values (4, 'Mobile', 'Vanaja', DATE('2019-03-01'), TIME('10:10:52'), 9000, 'Chennai');
insert into sales values (5, 'Headset', 'Jalaja', DATE('2019-04-06'), TIME('11:08:59'), 6000, 'Goa');

Following is another example of this function —

mysql> SELECT ProductName, CustomerName, DispatchDate, Price, DATEDIFF(CURRENT_DATE, DispatchDate) as difference_in_days, TIMEDIFF(CURRENT_TIME, DispatchTime) as time_difference FROM sales;
+-------------+--------------+--------------+-------+--------------------+-----------------+
| ProductName | CustomerName | DispatchDate | Price | difference_in_days | time_difference |
+-------------+--------------+--------------+-------+--------------------+-----------------+
|   Key-Board |         Raja |   2019-09-01 |  7000 |                678 |        10:07:37 |
|   Earphones |         Roja |   2019-05-01 |  2000 |                801 |        10:07:37 |
|       Mouse |         Puja |   2019-03-01 |  3000 |                862 |        10:07:38 |
|      Mobile |       Vanaja |   2019-03-01 |  9000 |                862 |        10:56:45 |
|     Headset |       Jalaja |   2019-04-06 |  6000 |                826 |        09:58:38 |
+-------------+--------------+--------------+-------+--------------------+-----------------+
5 rows in set (0.00 sec)

Example 7

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

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

Following query calculates and displays the remaining number of days and time for the subscription to complete —

mysql> SELECT SubscriberName, PackageName, DATEDIFF(CURRENT_DATE, SubscriptionDate) as RemainingDays, TIMEDIFF(CURRENT_TIME, SubscriptionTime) as RemainingTime FROM SubscribersData;
+----------------+-------------+---------------+---------------+
| SubscriberName | PackageName | RemainingDays | RemainingTime |
+----------------+-------------+---------------+---------------+
|           Raja |     Premium |           262 |      00:59:17 |
|           Roja |       Basic |           226 |      11:39:47 |
|           Puja |    Moderate |           125 |      16:09:46 |
|         Vanaja |       Basic |           139 |      05:16:27 |
|         Jalaja |     Premium |           161 |      09:07:21 |
+----------------+-------------+---------------+---------------+
5 rows in set (0.00 sec)