SQLTerminal.app
now available

XPath.app
now available
Want fresh tech tips in your inbox?

Managing Timestamps in MySQL with a Trigger

       

MySQL doesn't support having two columns with time stamping on both initialization and/or on updating at the same time. It would be nice to be able to do this where the created_at column gets the current_timestamp on initialization and the updated_at gets changed on updating the row.

like so doesn't work...

create table entries( body blob, created_at datetime default current_timestamp, updated_at timestamp default current_timestamp on update current_timestamp );

Seems like a feature a lot of folks would like. There are two work-arounds. The first is baking it into your application code with something like


create table entries(
  body blob,
  created_at datetime default null,
  updated_at timestamp default current_timestamp on update current_timestamp
);
insert into entries (body, created_at) values ('hello world', now());

The second way is to create a trigger and call the trigger on your insert action on a row.


create table entries (
  body  blob,
  created_at datetime default null,
  updated_at timestamp default null on update current_timestamp
);
create trigger init_created_at before insert on entries for each row set new.created_at = now();
Now whenever a new row is created the trigger will be executed and set the time to the current timestamp. You can forget about the created_at column in your code because it's not meant to be changed.

Tagged w/ #databases #mysql #sql #timestamping #triggersdatabases

Apps I've Built

XPath Expression Editor
Practice and improve your XPath skills with XPath Editor
Click to buy on the App Store
Photo Location Changer
Easily change the location on your photos and videos
Click here for more info about the app
Photo Date Changer
Easily change the dates and times on your photos and videos
Click here for more info about the app
VocabReminder
English dictionary with notifications so you won't forget what you're studying!
Click here for more info about the app
VocabQuiz
The app that quizzes and scores you on your vocabulary!
Click here for more info about the app