MySQL Timestamp

Introduction to MySQL Timestamp

MySQL Timestamp can be defined as a time-based MySQL data type containing date with time together. Timestamp supports Universal Time Coordinated(UTC) for MySQL. The Timestamp is written in a format that is set at 19 characters: YYYY-MM-DD HH:MM: SS. The values for Timestamp data type ranges from date 1^st January 1970 UTC to 19^th January 2038 UTC. Here, UTC refers to a coordinated time scale, conserved by the Bureau International des Poids et Mesures (BIPM). UTC is furthermore acknowledged by “Z time” or “Zulu Time”. When a value for Timestamp is inserted in the table in MySQL, then before inserting it MySQL converts in UTC form which is set to your time zone connection so that the value can then be stored in the database table. Whereas if we execute a query to access the Timestamp then, the server MySQL changes the UTC Timestamp value reversing to the respective time zone connection. This can be essential to outlook the Timestamp value which helps to know in time zone format. Remember that this type of conversion only takes place in Timestamp data type but not for others like DATETIME MySQL data type. By default, the time zone connection is the same as the time zone o MySQL server. Suppose when you connect to MySQL Server, you can apply a dissimilar time zone.

Syntax

The Timestamp()function which denotes a data type returns a DateTime based value.

The basic syntax of Timestamp is as follows:

Timestamp (exp, time)

Here, the two parameters represent these two values in the syntax:

• exp is required which denotes expression which has a date or DateTime value.
• time is an optional time value to add to the first parameter expression in the syntax above.

Since we know, the MySQL Timestamp function has a DateTime value, if there are two parameters then; the function initially complements the second parameter to the initial one.

How does Timestamp data type work in MySQL?

Based on the Timestamp function syntax, the two arguments of the function Timestamp result from a DateTime value and used with SELECT clause to fetch the result as below:

SELECT TIMESTAMP (“2020-03-26”, “10:15:11”);

Here, the function converts the expression to a DateTime value and besides a time interval is added to the value also. The result will be simply in the format of Timestamp as:

When you want to fetch any Timestamp MySQL value which was entered by the user located at a different location having unlike time zone, then the value that will be retrieved will not be identical to the value present in the database. If you don’t alter the time zone, then the similar Timestamp MySQL value will be fetched that you have stored. This happens because the time zone is not identical that is used for conversion. But for data type DATETIME, the value remains unchanged.

Examples to Implement MySQL Timestamp

Let us explain the Timestamp keyword and how it handles the values by the following examples:

Example #1

MySQL Timestamp time zone: Suppose, we have created a table named demo_timestamp where we have included a column with Timestamp data type named asts1.

Code:

CREATE TABLE demo_timestamp(ts1 TIMESTAMP);

Now, let us enter a Timestamp MySQL value into the demo_timestamp table.

Code:

INSERT INTO demo_timestamp(ts1) VALUES('2020-03-27 03:20:01');

After inserting this Timestamp value, you can view the column value by using a SELECT SQL statement on the table:

Code:

SELECT ts1 FROM demo_timestamp;

Output:

Example #2

Initialize and update Timestamp Columns Automatically:

Let us study the following illustration where we have created a table‘Employee’:

Code:

CREATE TABLE Employee(Empid INT PRIMARY KEY, EmpName VARCHAR (255) NOT NULL, Creation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

In the table created, we have set a column Creation_timewhich refers to Timestamp data type and its default value denotes CURRENT_TIMESTAMP.

After this, let us insert a new record into the Employee table but we will not specify any value for column Timestampi.e. Creation_time

Code:

INSERT INTO Employee (Empid,EmpName) VALUES ('1','ABC');

Now, we will view the table record and see what value is stored in the Timestamp column by using the following SQL query:

Code:

SELECT * FROM Employee;

Output:

We see that the column has been automatically initialized by the current timestamp when the row has been inserted. This characteristic feature is known as Automatic Initialization.

Example #3

we will add a next column‘Update_time’ in the table:

Code:

ALTER TABLE Employee ADD COLUMN Update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

ON UPDATE keyword here is a clause that will update the time when there is anything modified in the column value.

Suppose, insert a new record:

Code:

INSERT INTO Employee (Empid,EmpName) VALUES('2','XYZ');

Now, viewing the record:

Code:

SELECT * FROM Employee;

Output:

Example #4

For this time, both Timestamp values are the same but when we make any update like:

Code:

UPDATE Employee SET EmpName = 'JKL' WHERE Empid = 2;

Again, select all from table to view the changes:

Code:

SELECT * FROM Employee;

Output:

Explanation: You can see that the value of the Update_time_at column changed to the date and time when the column value was updated while the Creation_time sets to be unchanged showing insertion time value. This feature is known as Automatic Updating in MySQL for TIMESTAMP function. Remember if we update again with the same value then, the Timestamp value for Update_time column will not change.

Advantages of using Timestamp in MySQL

• Timestamp function adds data and time column to the table to manage all the activities relating to insertion, deletion, or updating of any value serving as Logs.
• Timestamp maintains Universal Time Coordinated(UTC) for MySQL to store the data and time values together and can be changed according to the client’s zone setting.
• Timestamp needs only 4 bytes’ storage capacity and contains a trailing fractional seconds part in up to microseconds i.e. 6 digits’exactness.

Conclusion

The Timestamp in MySQL returns the value that comprises of both date and time parts. It helps to know the date and time when any value is inserted into a table or any changes made. MySQL Timestamp data type column has a feature of automatic initialization and updating. CURRENT_TIMESTAMP function then, we get the current date and time of any operating system of the server on which the SQL executing.

Recommended Articles

This is a guide to MySQL Timestamp. Here we discuss an introduction to MySQL Timestamp, syntax, How does it work, examples, and advantages. You can also go through our other related articles to learn more –

1. SQL Data Types
2. Guide to MySQL Database Repair
3. Guide to SQL While Loop
4. IF Statement in MySQL