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. 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.Īutomatic Create and Modified timestamps in MySQL 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. 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.
Next, we must create a trigger, which will automatically be fired when we insert a value into our table and set the created timestamp.ĭROP TRIGGER IF EXISTS `my_table_insert_trigger`// 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).
So, in order to get a created timestamp, firstly we must add a DATETIME field to the table.ĪLTER TABLE my_table ADD CreatedTime datetime NOT NULL 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. On the latest versions of MySQL it is apparently possible to create a DateTime field with a default value of CURRENT_TIMESTAMP. So, to add your modified timestamp field to an existing table, all you need is:ĪLTER TABLE my_table ADD ModifiedTime TIMESTAMP Adding a CreatedTimeĪdding a CreateTime value is a little more involved.
#MYSQL TIMETAG ON CREATIONS UPDATE#
If your UPDATE query contains a value for your ModifiedTime field, this value will be used.While you can have multiple TIMESTAMP fields in a row, only one of these can be automatically updated with the current time on update.There are a couple of things to be aware of: 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. Adding a ModifiedTimeĪdding a modified timestamp to a table is the most straight forward.
Thankfully, a couple of MySQL’s internal features came to the rescue. 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. 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. 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. If you’re anything like me, you often find yourself needing to add create and modified timestamps to data stored in a MySQL database.
0 kommentar(er)
