MySQL Date and Time Fields
How to create a TIMESTAMP/DATETIME column in MySQL to automatically be set with the current time
The TIMESTAMP data type is the only way to have MySQL automatically set the time when a row was inserted and/or updated. DATETIME columns can’t do this.
TIMESTAMP columns are identical to DATETIME columns with one important exception — they can be set to take the current time value when a row is created or updated.
You can define more than one TIMESTAMP colum in a table — however, only ONE TIMESTAMP column in a table can be configured for Auto-Update or Auto-Initialization.
(For that reason, I usually make a practice of setting other columns to ‘DATETIME’ so there’s only one ‘TIMESTAMP’ column per table.)
There are four options when using TIMESTAMP, they are:
- Auto-initialization and auto-update:
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
- Auto-initialization only:
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
- Auto-update only:
ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
- Neither:
ts TIMESTAMP DEFAULT 0
Here’s an example of a complete ‘CREATE TABLE’ statement that shows how it all works:
CREATE TABLE `foo`.`timestamp_example` ( `id` INTEGER(10) UNSIGNED AUTO_INCREMENT, `some_text_field` VARCHAR(20), `updated_at` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `created_at` DATETIME DEFAULT NULL, PRIMARY KEY (id) );
Note that only one of the fields can be TIMESTAMP, that’s a limitation of MySQL.
The choice to use the TIMESTAMP on the ‘updated_at’ field was random in this example. Choose whatever makes sense for your application.
Click here to go to the table of contents for all MySQL Programming Example Code
My Next Thrilling Project
8/15/2014 – Stay tuned, as I climb up into my attic, cut a hole in the ceiling and install a vent for the kitchen. Also on tap is replacing two canister lights. All in preparation for sanding, smoothing and painting the kitchen and dining room ceiling. I can hardly stand the excitement.
8/23/2014 – Alright – I filled the gaping hole. Now I need to smooth it out. Plan is to put in two ceiling lights instead of just this one.
OMG My freakin back is killing me. One too many trips to the attic, but it’s done for now. There are more kitchen lights to replace, but these were the two biggies. Now to smooth out the ceiling….tomorrow.
Aug 28 – The third light is in and the ceiling patching is sanded. Might have to do one more sanding with a light sandpaper before painting.
There are also two remaining lights to replace. I’m going to wait till it gets a little cooler in the attic for that though.
Recent Plumbing Fix
Just recently, I started researching my options for fixing an outdoor water spigot. I came across a product called Sharkbite. These fixtures are great. You can do plumbing without having to use solder. It’s a little scary, but they seem to work great. You just cut the pipes and push them on. Here’s a picture.
Slate Virtual Buss Compressors
I got the Slate Virtual Buss Compressors the other day. They are really great.
Click Here