MySQL - UTC_TIMESTAMP() 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 UTC_TIMESTAMP() function is used to get the current UTC 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 –

UTC_TIMESTAMP([fsp]);

Example 1

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

mysql> SELECT UTC_TIMESTAMP();
+---------------------+
| UTC_TIMESTAMP()     |
+---------------------+
| 2021-07-18 15:34:22 |
+---------------------+
1 row in set (0.00 sec)

Example 2

Following is an example of this function in numerical context –

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

Example 3

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

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

Example 4

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

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

Example 5

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

mysql> SELECT UTC_TIMESTAMP;
+---------------------+
| UTC_TIMESTAMP       |
+---------------------+
| 2021-07-18 15:35:43 |
+---------------------+
1 row in set (0.00 sec)

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

Example 6

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 UTC_TIMESTAMP(5);
+---------------------------+
| UTC_TIMESTAMP(5)          |
+---------------------------+
| 2021-07-18 15:39:41.76880 |
+---------------------------+
1 row in set (0.00 sec)

Example 7

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, UTC_TIMESTAMP) as Difference FROM dispatches_data;
+-------------+--------------+---------------------+-------+------------+
| ProductName | CustomerName | DispatchTimeStamp   | Price | Difference |
+-------------+--------------+---------------------+-------+------------+
| Key-Board   | Raja         | 2019-05-04 15:02:45 | 7000  | 1160673    |
| Earphones   | Roja         | 2019-06-26 14:13:12 | 2000  | 1084402    |
| Mouse       | Puja         | 2019-12-07 07:50:37 | 3000  | 848625     |
| Mobile      | Vanaja       | 2018-03-21 16:00:45 | 9000  | 1749575    |
| Headset     | Jalaja       | 2018-12-30 10:49:27 | 6000  | 1340926    |
+-------------+--------------+---------------------+-------+------------+
5 rows in set (0.08 sec)

Example 8

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, UTC_TIMESTAMP) as RemainingDays FROM SubscriberDetails;
+----------------+-------------+---------------+
| SubscriberName | PackageName | RemainingDays |
+----------------+-------------+---------------+
| Raja           | Premium     | 269           |
| Roja           | Basic       | 234           |
| Puja           | Moderate    | 133           |
| Vanaja         | Basic       | 146           |
| Jalaja         | Premium     | 169           |
+----------------+-------------+---------------+
5 rows in set (0.02 sec)