Posted on
by

If you’re anything like me, you often find yourself needing to add create and modified timestamps to data stored in a MySQL database. Typically, I’d do this programmatically from within the application without giving it a second thought, wrapping up time(); in an SQL statement and firing it over.

Recently, while doing some development work for one of my clients, I was given the requirement to add Created and Modified timestamps to a whole bunch of existing data tables. These tables were referenced by hundreds of different MySQL queries, and to complicate matters further, we were in the middle of migrating the code over to a new database library.

I didn’t much fancy rewriting all of the SQL queries, potentially twice, just to add a couple of timestamps, so modifying code wasn’t much of an option. Thankfully, a couple of MySQL’s internal features came to the rescue.

Adding a ModifiedTime

Adding a modified timestamp to a table is the most straight forward. All your have to do is create the field of type TIMESTAMP, and by default, MySQL will automatically update the field when the row is modified.

There are a couple of things to be aware of:

  • While you can have multiple TIMESTAMP fields in a row, only one of these can be automatically updated with the current time on update.
  • If your UPDATE query contains a value for your ModifiedTime field, this value will be used.

So, to add your modified timestamp field to an existing table, all you need is:

ALTER TABLE my_table ADD ModifiedTime TIMESTAMP;

Adding a CreatedTime

Adding a CreateTime value is a little more involved.

On the latest versions of MySQL it is apparently possible to create a DateTime field with a default value of CURRENT_TIMESTAMP. This wasn’t an option for me as I was having to support a somewhat older version, besides, even on the newer versions of MySQL it is not possible to have more than one field using CURRENT_TIMESTAMP, which of course we are in order to get ModifiedTime working.

So, in order to get a created timestamp, firstly we must add a DATETIME field to the table.

ALTER TABLE my_table ADD CreatedTime datetime NOT NULL;

Note, that this must be created as NOT NULL in order for the next part to work (this is because setting NOT NULL forces an automatic all zeros default).

Next, we must create a trigger, which will automatically be fired when we insert a value into our table and set the created timestamp.

DELIMITER //
DROP TRIGGER IF EXISTS `my_table_insert_trigger`//
CREATE TRIGGER `my_table_insert_trigger`
BEFORE INSERT ON `my_table`
FOR EACH ROW
BEGIN
IF NEW.CreatedTime = '0000-00-00 00:00:00' THEN
SET NEW.CreatedTime = NOW();
END IF;
END;//
DELIMITER ;

Now, when you insert a value into the table, this trigger will fire and, if you’ve not provided a CreatedTime field in your insert query, it will be set to the current time stamp.

Conclusion

Since all the queries in the application code specified the columns they were updating on insert, I was able to use this method to add created and modified time stamp fields to all the existing object tables in the database, without needing to modify any of the existing application code.

This simplified my life greatly, but also suggests to me that this method might be somewhat more efficient than the methods I’d previously used.

14 thoughts on “Automatic Create and Modified timestamps in MySQL”

  1. It is not necessarily a good idea to mix datetime with timestamp. For example, using your code I have a record that has the created date displayed as one hour earlier than the modified date, even though I have only inserted the record.

    The timestamp datatype is converted to UTC on save and to the local time when selected. To make this work consistently you should change the CreatedTime and ModifiedTime like this;

    `CreatedTime` timestamp DEFAULT 0
    `ModifiedTime` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

    Then the rest of your code works consistently.

    Thanks for the great post!

  4. Thanks for the solution.
    A little correction, the if-statement within the create-trigger-statement is acutally not necessary, because the insert-trigger won’t be fired twice for each line. The trigger-statement will only be executed once when a new row is inserted into the table, not for every insert of a value.

  5. This is great. This is what I come across and I want to share.

    In mysql version 5.6.x (I’m using 5.6.19), there is no need to write a trigger, instead just add a default value for createdtime.

    ALTER TABLE tblname
    ADD COLUMN modifiedtime TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ,
    ADD COLUMN createdtime DATETIME NULL DEFAULT CURRENT_TIMESTAMP;

    I allowed null, to keep my existing records to stay with null to differentiate that those records are before I implement these 2 fields.

    This didn’t work in mysql version 5.5.x. So, this article authors solution works for mysql version 5.5.x and before.

    Thanks.

  6. Automatic Create and Modified timestamps in MySQL

    Posted on March 11, 2013
    by Marcus Povey

    If you’re anything like me, you often find yourself needing to add create and modified timestamps to data stored in a MySQL database. Typically, I’d do this programmatically from within the application without giving it a second thought, wrapping up time(); in an SQL statement and firing it over.
    Recently, while doing some development work for one of my clients, I was given the requirement to add Created and Modified timestamps to a whole bunch of existing data tables. These tables were referenced by hundreds of different MySQL queries, and to complicate matters further, we were in the middle of migrating the code over to a new database library.
    I didn’t much fancy rewriting all of the SQL queries, potentially twice, just to add a couple of timestamps, so modifying code wasn’t much of an option. Thankfully, a couple of MySQL’s internal features came to the rescue.
    Adding a ModifiedTime
    Adding a modified timestamp to a table is the most straight forward. All your have to do is create the field of type TIMESTAMP, and by default, MySQL will automatically update the field when the row is modified.
    There are a couple of things to be aware of:
    While you can have multiple TIMESTAMP fields in a row, only one of these can be automatically updated with the current time on update.
    If your UPDATE query contains a value for your ModifiedTime field, this value will be used.
    So, to add your modified timestamp field to an existing table, all you need is:

    ALTER TABLE my_table ADD ModifiedTime TIMESTAMP;

    Adding a CreatedTime
    Adding a CreateTime value is a little more involved.
    On the latest versions of MySQL it is apparently possible to create a DateTime field with a default value of CURRENT_TIMESTAMP. This wasn’t an option for me as I was having to support a somewhat older version, besides, even on the newer versions of MySQL it is not possible to have more than one field using CURRENT_TIMESTAMP, which of course we are in order to get ModifiedTime working.
    So, in order to get a created timestamp, firstly we must add a DATETIME field to the table.

    ALTER TABLE my_table ADD CreatedTime datetime NOT NULL;

    Note, that this must be created as NOT NULL in order for the next part to work (this is because setting NOT NULL forces an automatic all zeros default).
    Next, we must create a trigger, which will automatically be fired when we insert a value into our table and set the created timestamp.

    DELIMITER //
    DROP TRIGGER IF EXISTS     my_table_insert_trigger//
    CREATE TRIGGER my_table_insert_trigger
    BEFORE INSERT ON my_table
    FOR EACH ROW
    BEGIN
    IF NEW.CreatedTime = '0000-00-00 00:00:00' THEN
    SET NEW.CreatedTime = NOW();
    END IF;
    END;//
    DELIMITER ;

    Now, when you insert a value into the table, this trigger will fire and, if you’ve not provided a CreatedTime field in your insert query, it will be set to the current time stamp.
    Conclusion
    Since all the queries in the application code specified the columns they were updating on insert, I was able to use this method to add created and modified time stamp fields to all the existing object tables in the database, without needing to modify any of the existing application code.
    This simplified my life greatly, but also suggests to me that this method might be somewhat more efficient than the methods I’d previously used.

    Share this:

    Email
    LinkedIn
    Twitter
    Google
    Facebook
    Reddit

    Posted in Software | Tagged howto, modified, mysql, sql, timestamp, triggers, update | 5 CommentsPost navigation
    ← Here we go again, again…
    Encrypted client side PHP sessions →

    5 thoughts on “Automatic Create and Modified timestamps in MySQL”

    Dan Ware says:
    September 26, 2013 at 12:16 pm

    It is not necessarily a good idea to mix datetime with timestamp. For example, using your code I have a record that has the created date displayed as one hour earlier than the modified date, even though I have only inserted the record.
    The timestamp datatype is converted to UTC on save and to the local time when selected. To make this work consistently you should change the CreatedTime and ModifiedTime like this;
    CreatedTime timestamp DEFAULT 0ModifiedTime timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    Then the rest of your code works consistently.
    Thanks for the great post!

    MarcoK says:
    July 7, 2014 at 10:52 am

    Thanks for the solution.
    A little correction, the if-statement within the create-trigger-statement is acutally not necessary, because the insert-trigger won’t be fired twice for each line. The trigger-statement will only be executed once when a new row is inserted into the table, not for every insert of a value.

    Firnas Aliyar says:
    November 20, 2014 at 2:02 pm

    This is great. This is what I come across and I want to share.
    In mysql version 5.6.x (I’m using 5.6.19), there is no need to write a trigger, instead just add a default value for createdtime.
    ALTER TABLE tblname
    ADD COLUMN modifiedtime TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ,
    ADD COLUMN createdtime DATETIME NULL DEFAULT CURRENT_TIMESTAMP;
     

  7. i want to create a script in mysql , condition is if my table is not updated or inserted for one hour,i should get any sms ..alert

  8. Not sure about pure mysql, but you could do this with a bit of scripting: I’d approach it by querying the latest item – “`select * from table order by timestamp desc limit 1“`, and if that timestamp is > one hour ago then send your sms.

  9. Just found this solution and wanted to send my many thanks. I use Bluehost for my hosting, and they use MySQL 5.5 – which doesn’t support this natively (so to speak).

  11. Hi, i’m new with this thing, tried to add timestamp to check if there’s alien intrussion on my database. When i tried to add triggers, i got this

    There seems to be an er ror in your SQL query. The MySQL server er ror output below, if there is any, may also help you in diagnosing the problem.

    ERROR: Unknown Punctuation String @ 11
    STR: //
    SQL: DELIMITER //
    DROP TRIGGER IF EXISTS wp_comments_insert_trigger//
    # MySQL retur ned an empty re sult set (i.e. zero rows).
    CREATE TRIGGER wp_comments_insert_trigger
    BEFORE INSERT ON wp_comments
    FOR EACH ROW
    BEGIN
    IF NEW.CreatedTime = ‘0000-00-00 00:00:00’ THEN
    SET NEW.CreatedTime = NOW();
    END IF;
    END;//
    # MySQL returned an empty res ult set (i.e. zero rows).
    DELIMITER ;

    SQL query:

    DELIMITER ;

    MySQL said: Documentation
    #1064 – Y ou have an err >or in your SQL syn tax; check the manual that corresponds to your My SQL server version for the right syn tax to use near ‘DELIMITER’ at line 1

    Additional data:
    mys ql version 5.5.42-37.1
    Although i’m getting this error, CreatedTime works fine, it can record the creation time of a row, but i a little bit worried why it’s getting this error. Do i need to worry about this? Do you have a solution? Thanks a lot!

  14. Hi,

    Thanks for sharing that’s great and works great for me. Question, do you have any similar for updated by, so I can see the user who made the change?

    Much appreciated 🙂

Leave a Reply