EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Timestamp
 

MySQL Timestamp

Aanchal Sharma
Article byAanchal Sharma
EDUCBA
Reviewed byRavi Rathore

Updated May 15, 2023

MySQL Timestamp

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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.

Note: For this article, we have used the PostgreSQL server.

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:

MySQL Timestamp1

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:

MySQL Timestamp2

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:

MySQL Timestamp3

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:

records

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:

view the changes

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.

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW