MySQL - CURRENT_TIMESTAMP() Function



The TIMESTAMP datatype in MySQL is used to store the timestamp value. A timestamp in MySQL is nothing but a numerical value denoting the time when an event occurred. It is represented as the number of milliseconds counted from the Epoch of a system, i.e. '1970-01-01 00:00:01' UTC to the specified time. MySQL provides a function called CURRENT_TIMESTAMP() to extract the current time.

MySQL CURRENT_TIMESTAMP() Function

The MySQL CURRENT_TIMESTAMP() function is the synonym for NOW(). It is used to get the current date and time value local to a system. 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();

Parameters

This method does not accept any parameters.

Return value

The function returns a timestamp value representing the current date and time. The format of the timestamp is 'YYYY-MM-DD HH:MM:SS'.

Example

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

SELECT CURRENT_TIMESTAMP();

Output

Following output is obtained −

CURRENT_TIMESTAMP()
2021-07-10 22:11:24

Example

Following is an example of this function in numerical context −

SELECT CURRENT_TIMESTAMP()+0;

Output

Following output is obtained −

CURRENT_TIMESTAMP()+0
20210710221124

Example

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

SELECT CURRENT_TIMESTAMP()+12;

Output

Following output is obtained −

CURRENT_TIMESTAMP()+12
20210710221136

Example

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

SELECT CURRENT_TIMESTAMP()-12;

Output

Following output is obtained −

CURRENT_TIMESTAMP()-12
20210710221112

Example

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

SELECT CURRENT_TIMESTAMP;

Following output is obtained −

CURRENT_TIMESTAMP
2021-07-10 22:11:24

In Numeric context

SELECT CURRENT_TIMESTAMP+0;

Following output is obtained −

CURRENT_TIMESTAMP+0
20210710221124

Example

In this example, we are creating a table named ORDERS using the following CREATE TABLE query −

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

Now, we can insert the records into the ORDERS table using 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);

Following query is another example of this function −

SELECT OID, DATE, 
TIMESTAMPDIFF(MINUTE, DATE, CURRENT_TIMESTAMP) AS TIMESTAMPDIFF, 
AMOUNT FROM ORDERS;

Output

The output is displayed as follows −

OID DATE TIMESTAMPDIFF AMOUNT
102 2009-10-08 00:00:00 7389699 3000.00
100 2009-10-08 00:00:00 7389699 1500.00
101 2009-11-20 00:00:00 7327779 1560.00
103 2008-05-20 00:00:00 8118339 2060.00

Example

Following query calculates and displays the remaining number of days for the subscription to complete −

SELECT OID, DATE, 
TIMESTAMPDIFF(DAY, DATE, CURRENT_TIMESTAMP) AS TIMESTAMPDIFF, 
AMOUNT FROM ORDERS;

Output

The output is displayed as follows −

OID DATE TIMESTAMPDIFF AMOUNT
102 2009-10-08 00:00:00 5131 3000.00
100 2009-10-08 00:00:00 5131 1500.00
101 2009-11-20 00:00:00 5088 1560.00
103 2008-05-20 00:00:00 5637 2060.00
Advertisements