MySQL - LOCALTIME() Function, LOCALTIME

---

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

Syntax

Following is the syntax of the above function –

LOCALTIME();

Example 1

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

mysql> SELECT LOCALTIME();
+---------------------+
| LOCALTIME()         |
+---------------------+
| 2021-07-11 18:07:06 |
+---------------------+
1 row in set (0.00 sec)

Example 2

Following is an example of this function in numerical context –

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

Example 3

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

mysql> SELECT LOCALTIME()+12;
+----------------+
| LOCALTIME()+12 |
+----------------+
| 20210711180759 |
+----------------+
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 LOCALTIME()-12;
+----------------+
| LOCALTIME()-12 |
+----------------+
| 20210711180792 |
+----------------+
1 row in set (0.00 sec)

Example 5

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

mysql> SELECT LOCALTIME;
+---------------------+
| LOCALTIME           |
+---------------------+
| 2021-07-11 18:08:50 |
+---------------------+
1 row in set (0.00 sec)
mysql> SELECT LOCALTIME+0;
+----------------+
| LOCALTIME+0    |
+----------------+
| 20210711180907 |
+----------------+
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 is another example of this function —

mysql> SELECT ProductName, CustomerName, DispatchTimeStamp, Price, TIMESTAMPDIFF (MINUTE, DispatchTimeStamp, LOCALTIME) as Difference FROM dispatches_data;
+-------------+--------------+---------------------+-------+------------+
| ProductName | CustomerName | DispatchTimeStamp   | Price | Difference |
+-------------+--------------+---------------------+-------+------------+
| Key-Board   | Raja         | 2019-05-04 15:02:45 | 7000  | 1150746    |
| Earphones   | Roja         | 2019-06-26 14:13:12 | 2000  | 1074476    |
| Mouse       | Puja         | 2019-12-07 07:50:37 | 3000  | 838698     |
| Mobile      | Vanaja       | 2018-03-21 16:00:45 | 9000  | 1739648    |
| Headset     | Jalaja       | 2018-12-30 10:49:27 | 6000  | 1331000    |
+-------------+--------------+---------------------+-------+------------+
5 rows in set (0.00 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 calculates and displays the remaining number of days for the subscription to complete —

mysql> SELECT SubscriberName, PackageName, TIMESTAMPDIFF(DAY, SubscriptionTimeStamp, LOCALTIME) as RemainingDays FROM SubscriberDetails;
+----------------+-------------+---------------+
| SubscriberName | PackageName | RemainingDays |
+----------------+-------------+---------------+
| Ram            | Premium     | 262           |
| Rahman         | Basic       | 227           |
| Robert         | Moderate    | 126           |
| Radha          | Basic       | 140           |
| Rajiya         | Premium     | 162           |
+----------------+-------------+---------------+
5 rows in set (0.00 sec)