How to Update Current Timestamp in MySQL?

MySQL is an easy-to-use RDBMS. Many organizations prefer to use it because of its easy maintainability, easier to prepare schemas, stored procedures, triggers, and database maintenance. In this article, let us see how to update to Current Timestamp in MySQL.

Step 1: Database creation 

Firstly we create the database.Here GEEKSFORGEEKS is the db name.

Query:

CREATE DATABASE GEEKSFORGEEKS;

Step 2: Make the database active

Query:

USE GEEKSFORGEEKS;

Step 3: Create a table employee with empLoginTime field as Timestamp.

Query:

CREATE TABLE employee
   (
       empId int NOT NULL,
       empName VARCHAR(20),
       empLoginTime TIMESTAMP
   );

Output:

Step 4: We can set the time zone as follows

Query:

SET TIME_ZONE = '+00:00';

If we want to change the time Zone that also can be done.

Step 5: Now, let us insert the records

Query:

INSERT INTO employee
    (
        empId, empName, empLoginTime
    )
VALUES
    (1, 'XXX', '2021-01-01 00:00:01'),
    (2, 'YYY', '2021-01-01 00:00:01'),
    (3, 'ZZZ', '2021-01-01 00:00:01'),
    (4, 'XYZ', '2021-01-01 00:00:01');

Step 6: Display the records

Query:

SELECT * FROM employee;

Output:

Here, the ”empLoginTime’ column indicates the value in the mentioned timezone pattern.

Step 7: There are possibilities of viewing data from different timezones. During those times, we can set the timezone as we like

Query:

SET time_zone ='+03:00';
SELECT * FROM employee;

Output:

In MySQL, there is a facility to keep the default current timestamp value for a column. i.e. empLoginTime should automatically get the timestamp value whenever the employee login into the system.  It is equivalent to updating the column to a current timestamp value.

Query:

ALTER TABLE employee MODIFY 
COLUMN empLoginTime 
TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP;    

Let us check the structure of the table now

Query:

DESC employee;

Advantage of having the update of current_timestamp:

With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP, the “empLoginTime” column has the current timestamp for its default value and is automatically updated to the current timestamp.

Now let us insert the records in the table. Since we have the default current_timestamp and on update also to current_timestamp, we no need to give the values for ’empLoginTime’. Below is the query to do that.

Query:

**Since empLoginTime has the current 
timestamp value, we no need to add the value
 explicitly to that column**
INSERT INTO employee (empId, empName) VALUES
(5, 'ABC'),(6,'CDE');

Display the records

Query:

SELECT * FROM employee;

It is always better to have the ‘login’ and ‘logout’ columns and also similar kinds of columns to have to default current_timestamp values. If it is not set, we can update the column values easily.  

In MySQL workbench, we have to do the following :

Steps to update the data

• Navigate to Edit –> Preferences.
• Then click the “SQL Editor” tab and uncheck the “Safe Updates” check box.
• Then click on Query –> Reconnect to Server.

Now execute your SQL query

Query:

UPDATE employee set empLoginTime = CURRENT_TIMESTAMP
WHERE empId < 4;

Display the records 

Query:

SELECT * FROM employee;

Usually, we should have “created time” and “modified time” in a table to have the updated values. That will help for the auditing purpose. The default constraint is helpful to set the current timestamp value. During table creation itself, the default value can be set, or else by using ALTER  command, we can set the default constraint. The other way is by using the ‘UPDATE’ command and which is seen in the above example.