mySQL created and updated timestamp

Creating fields with time stamps that contains the creation and / or the last modification dates in MySQL using PHPMyAdmin is straightforward.


Phpmyadmin create table current timestamp

Creating fields with time stamps that contains the creation and / or the last modification dates in MySQL using PHPMyAdmin is straightforward.

No need for specific SQL code in latest versions, as it is now possible to have two different fields in the same table with current timestamp as default, and therefore no need to use triggers anymore for this purpose.

In the table creation interface, add two fields – one for the creation date, and one for the modification – with options as described : both as timestamp,  current timestamp as default   value, and attribute “on update” for the last modification date.

Once created, table structure should be similar to below screenshot.

MySQL table creation date

Give it a try by creating a new table entry :

In the browsing interface, see your entry newly created – both creation and modification dates are equal.

Try updating one of the values of one entry:

And see directly the change : modification date has been updated to match change time, and creation time hasn’t.

It is of course also possible to do it using SQL, here’s the corresponding code :

How to set a MySQL current timestamp on create and update

To set a MySQL current timestamp on table create and table update, when creating a table, put two different fields with the type TIMESTAMP, and the default value MYSQL current timestamp, also called current time. The modification date field should also have the attribute on update default timestamp.

The first field will be the creation timestamp, and must be with type timestamp, and default set to current time. When creating a new record, that field will be filled in with the MySQL current timestamp.

Learn SEO Basics: Enroll Today!

Boost your website's visibility and traffic by mastering the fundamentals of SEO with our easy-to-follow Basics course.

Start Learning SEO

Boost your website's visibility and traffic by mastering the fundamentals of SEO with our easy-to-follow Basics course.

The second field will be the modification timestamp, and must also be set with type timestamp, plus with the attribute on update CURRENT_TIMESTAMP. That way, when creating a record, the modification date will be set to MySQL current timestamp. And also, whenever the record will be modified, the modification timestamp field will be updated as well with the MySQL current timestamp.

Automatic Initialization and Updating for TIMESTAMP and DATETIME

What is a MySQL timestamp?

The  MySQL timestamp   is a way to represent a time and a date altogether, in the same unit of storage.

MySQL timestamp   ranges from first January 1970 in server UTC time, up to 19 January 2038.

This means that usage of the  MySQL timestamp   are limited, as they will need to be changed by year 2038, however most likely a patch will take care of that.

The  MySQL timestamp   is one of the two ways to store in a  MySQL database   both date and time, along with the datetime format.

It is usually better to use a  MySQL timestamp   to store dates such as modification and creation date of a record in the  MySQL database   as it is the favourite way to use date and time within a database.

However, all these values along with the standard date format are related, and can even work together.

Frequently Asked Questions

How to add MYSQL created and updated timestamp?
To create timestamps for the created and updated records in a MySQL table, you can define two fields with the TIMESTAMP type and set the default value to the current timestamp. For the updated timestamp, add the attribute ON UPDATE CURRENT_TIMESTAMP. This ensures the created timestamp is set when a new record is added and the updated timestamp reflects the current time whenever the record is modified.
What are the key steps to securely export and transfer a WordPress site to a new domain without losing SEO rankings?
The key steps include creating a full backup of the site, changing the domain settings and URL within the WordPress settings, properly redirecting URLs from the old domain to the new one to maintain SEO, and notifying Google of the change through Google Search Console. Ensuring minimal downtime and preserving all site data and SEO settings are crucial throughout the process.

Yoann Bierling
About the author - Yoann Bierling
Yoann Bierling is a Web Publishing & Digital Consulting professional, making a global impact through expertise and innovation in technologies. Passionate about empowering individuals and organizations to thrive in the digital age, he is driven to deliver exceptional results and drive growth through educational content creation.

Learn SEO Basics: Enroll Today!

Boost your website's visibility and traffic by mastering the fundamentals of SEO with our easy-to-follow Basics course.

Start Learning SEO

Boost your website's visibility and traffic by mastering the fundamentals of SEO with our easy-to-follow Basics course.



Comments (8)

 2018-08-19 -  Rudolph Henry
Lo pasé muy bien leyendo esta información, sigan con el buen trabajo
 2018-08-19 -  Kay Chapman
Grazie è stato utile
 2018-08-19 -  Kate Schultz
簡單,清晰,解釋得很好,非常感謝
 2018-08-19 -  Amy Coleman
簡直不敢相信我終於找到了解決方案,這是一場很長一段時間的噩夢,現在已經解決了
 2018-08-19 -  Ann Perez
ไซต์ที่เยี่ยมยอดโปรดไปต่อ
 2018-08-19 -  Eric Parker
Sonunda çözümü bulduğuma inanamıyorum, bu uzun zamandan beri bir kabustu, şimdi çözüldü
 2018-08-19 -  Janice Perry
Привет, я видел вашу статью, и это помогло мне решить мою проблему, спасибо большое
 2018-08-19 -  dias2zl
Tidak tahu bagaimana untuk mengucapkan terima kasih, tetapi itu adalah jenius murni, terima kasih

Leave a comment