MySQL - UTC_TIMESTAMP() Function

The MYSQL UTC_TIMESTAMP() function is used to get the current Coordinated Universal 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.

This MySQL function accepts no arguments, but an optional argument can still be passed to it, that specifies the number of digits to be displayed in the fractional part of seconds.

Syntax

Following is the syntax of MySQL UTC_TIMESTAMP() function −

UTC_TIMESTAMP();

Parameters

This method does not accept any parameters.

Return value

This function returns the current date and time in the UTC timezone. The format of the returned value is in the 'YYYY-MM-DD HH:MM:SS' format.

Example

In the following example, we are using the MySQL UTC_TIMESTAMP() function to fetch the current UTC time and date −

SELECT UTC_TIMESTAMP() As Result;

Output

This will produce the following result −

Result
2023-11-21 10:50:34

Example

We can also use UTC_TIMESTAMP instead of UTC_TIMESTAMP() to retrieve the current UTC time and date −

SELECT UTC_TIMESTAMP As Result;

Output

Following is the output of the above code −

Result
2023-11-21 10:50:34

Example

Here, we are adding seconds to the UTC current time and date as shown below in the below query −

SELECT UTC_TIMESTAMP()+12 As Result;

Output

Output of the above code is as follows −

Result
20231121105046

Example

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

SELECT UTC_TIMESTAMP()-12 As Result;

Output

We get the result as shown below −

Result
20231121105022

Example

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

SELECT UTC_TIMESTAMP(5) As Result;

Output

Following is the output of the above code −

Result
2023-11-21 10:50:34.06984

Example

In the following example, let us create a table named ORDERS using CREATE TABLE statement −

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2)
);

Now, insert the following records into the ORDERS table using the INSERT statement −

INSERT INTO ORDERS VALUES 
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

Execute the below query to fetch all the inserted records in the above-created table −

Select * From ORDERS;

Following is the ORDERS table −

OID DATE CUSTOMER_ID AMOUNT
102 2009-10-08 00:00:00 3 3000.00
100 2009-10-08 00:00:00 3 1500.00
101 2009-11-20 00:00:00 2 1560.00
103 2008-05-20 00:00:00 4 2060.00

The below query calculates the time difference in hours between the order date and the current UTC timestamp for each record in the ORDERS table −

SELECT OID, DATE, TIMESTAMPDIFF(HOUR, DATE, UTC_TIMESTAMP) 
As Result From ORDERS;

Output

The output is displayed as follows −

OID DATE Result
102 2009-10-08 00:00:00 123778
100 2009-10-08 00:00:00 123778
101 2009-11-20 00:00:00 122746
103 2008-05-20 00:00:00 135922
Advertisements