MySQL - NOW() 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 NOW() function is 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 –

NOW([fsp]);

Example 1

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

mysql> SELECT NOW();
+---------------------+
|               NOW() |
+---------------------+
| 2021-07-10 22:11:24 |
+---------------------+
1 row in set (0.00 sec)

Example 2

Following is an example of this function in numerical context –

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

Example 3

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

mysql> SELECT NOW()+12;
+------------------------+
|               NOW()+12 |
+------------------------+
| 20210710221136         |
+------------------------+
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 NOW()-12;
+-------------------+
|          NOW()-12 |
+-------------------+
| 20210710221112    |
+-------------------+
1 row in set (0.00 sec)

Example 5

This function accepts an optional argument i.e. fsp, using this you can specify the number of digits you need after the fraction for seconds.

mysql> SELECT NOW(3);
+-------------------------+
| NOW(3)                  |
+-------------------------+
| 2021-07-11 18:32:10.526 |
+-------------------------+
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, NOW()) as Difference FROM dispatches_data;
+-------------+--------------+---------------------+-------+------------+
| ProductName | CustomerName | DispatchTimeStamp   | Price | Difference |
+-------------+--------------+---------------------+-------+------------+
| Key-Board   | Raja         | 2019-05-04 15:02:45 | 7000  | 1149590    |
| Earphones   | Roja         | 2019-06-26 14:13:12 | 2000  | 1073319    |
| Mouse       | Puja         | 2019-12-07 07:50:37 | 3000  | 837542     |
| Mobile      | Vanaja       | 2018-03-21 16:00:45 | 9000  | 1738492    |
| Headset     | Jalaja       | 2018-12-30 10:49:27 | 6000  | 1329843    |
+-------------+--------------+---------------------+-------+------------+
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, NOW()) as RemainingDays FROM SubscriberDetails;
+----------------+-------------+---------------+
| SubscriberName | PackageName | RemainingDays |
+----------------+-------------+---------------+
| Raja           | Premium     | 262           |
| Roja           | Basic       | 226           |
| Puja           | Moderate    | 125           |
| Vanaja         | Basic       | 139           |
| Jalaja         | Premium     | 161           |
+----------------+-------------+---------------+
5 rows in set (0.00 sec)