Updated May 15, 2023
Introduction to MySQL Timestamp
MySQL Timestamp is a time-based MySQL data type containing date and time together. Timestamp supports Universal Time Coordinated(UTC) for MySQL. The format of the timestamp in MySQL is set to 19 characters: YYYY-MM-DD HH:MM:SS. Timestamp data type values range from 1st January 1970 UTC to 19th January 2038 UTC. UTC refers to a coordinated time scale conserved by the Bureau International des Poids et Mesures (BIPM). MySQL recognizes UTC as “Z time” or “Zulu Time”. MySQL converts a timestamp value to UTC format, adjusts it to the time zone connection, and then stores it in the database table when it is inserted into a table. Whereas if we execute a query to access the timestamp, 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 the Timestamp data type but not for others like the DATETIME MySQL data type. By default, the time zone connection is the same as the time zone o MySQL server. Connecting to MySQL Server allows you to apply a different time zone.
Syntax
The Timestamp()function, which denotes a data type, returns a DateTime-based value.
The basic syntax of a timestamp is as follows:
Timestamp (exp, time)Here, the two parameters represent these two values in the syntax:
- exp is required, which denotes an expression with a date or DateTime value.
- time is an optional time value to add to the first parameter expression in the syntax above.
As 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?
We can use the two arguments of the Timestamp function resulting from a DateTime value and combine it with a SELECT clause to retrieve the result as shown below:
SELECT TIMESTAMP ("2020-03-26", "10:15:11");Here, the function converts the expression to a DateTime value, and a time interval is added to the value. The result will be simply in the format of the timestamp:
When a user enters a timestamp value in MySQL from a location with a different time zone, and the value is fetched from another location with an unlike time zone, the retrieved value may not be identical to the value stored in the database. If you don’t alter the time zone, then the similar Timestamp MySQL value you have stored will be fetched. This issue occurs because the time zone used for conversion is not the same as the time zone being used. 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, which includes a column with a 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 the 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 Timestamp, i.e., Creation_time.
Code:
INSERT INTO Employee (Empid,EmpName) VALUES ('1','ABC');We can view the table record and check the value stored in the Timestamp column by executing the following SQL query:
Code:
SELECT * FROM Employee;Output:
We see that the current timestamp automatically initializes the column when the row has been inserted. We refer to this feature 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;The ON UPDATE keyword in this context is a clause that updates the time whenever there is a modification 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 the table to view the changes:
Code:
SELECT * FROM Employee;Output:
Explanation: You can see that the Update_time_at column value changed to the date and time when the column value was updated, whereas the Creation_time remains unchanged, indicating the insertion time value. This functionality is referred to as Automatic Updating in MySQL for the TIMESTAMP function. Remember, if we update again with the same value, the Timestamp value for the Update_time column will not change.
Advantages of using Timestamp in MySQL
- The timestamp function adds data and time columns to the table to manage all the activities relating to inserting, deleting, or updating any value serving as Logs.
- MySQL maintains Universal Time Coordinated (UTC) using Timestamp to store data and time values together, and we can modify it based on the client’s zone setting.
- Timestamp needs only 4 bytes’ storage capacity and contains a trailing fractional seconds part up to microseconds, i.e., 6 digits’exactness.
Conclusion
The Timestamp in MySQL returns the value comprising date and time parts. In MySQL, users can utilize a feature to determine the date and time of value insertion into a table or any changes that occur. This feature can be quite helpful. The MySQL Timestamp data type column features 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
We hope that this EDUCBA information on “MySQL Timestamp” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
