MySQL - CURRENT_TIMESTAMP() Function, CURRENT_TIMESTAMP

---

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_TIMESTAMP() 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 –

CURRENT_TIMESTAMP();

Example 1

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

mysql> SELECT CURRENT_TIMESTAMP();
+---------------------+
| CURRENT_TIMESTAMP() |
+---------------------+
| 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 CURRENT_TIMESTAMP()+0;
+-----------------------+
| CURRENT_TIMESTAMP()+0 |
+-----------------------+
|        20210710221124 |
+-----------------------+
1 row in set (0.00 sec)

Example 3

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

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

Example 4

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

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

Example 5

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

mysql> SELECT CURRENT_TIMESTAMP;
+---------------------+
|   CURRENT_TIMESTAMP |
+---------------------+
| 2021-07-10 22:11:24 |
+---------------------+
1 row in set (0.00 sec)
mysql> SELECT CURRENT_TIMESTAMP()+0;
+-----------------------+
|   CURRENT_TIMESTAMP+0 |
+-----------------------+
|        20210710221124 |
+-----------------------+
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, CURRENT_TIMESTAMP) 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, CURRENT_TIMESTAMP) 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)