Skip to Main Content
MySQL Cookbook
book

MySQL Cookbook

by Paul DuBois
October 2002
Intermediate to advanced content levelIntermediate to advanced
1024 pages
27h 26m
English
O'Reilly Media, Inc.
Content preview from MySQL Cookbook

Using TIMESTAMP Values

Problem

You want a record’s creation time or last modification time to be automatically recorded.

Solution

The TIMESTAMP column type can be used for this. However, it has properties that sometimes surprise people, so read this section to make sure you know what you’ll be getting. Then read the next few sections for some applications of TIMESTAMP columns.

Discussion

MySQL supports a TIMESTAMP column type that in many ways can be treated the same way as the DATETIME type. However, the TIMESTAMP type has some special properties:

  • The first TIMESTAMP column in a table is special at record-creation time: its default value is the current date and time. This means you need not specify its value at all in an INSERT statement if you want the column set to the record’s creation time; MySQL will initialize it automatically. This also occurs if you set the column to NULL when creating the record.

  • The first TIMESTAMP is also special whenever any columns in a row are changed from their current values. MySQL automatically updates its value to the date and time at which the change was made. Note that the update happens only if you actually change a column value. Setting a column to its current value doesn’t update the TIMESTAMP.

  • Other TIMESTAMP columns in a table are not special in the same way as the first one. Their default value is zero, not the current date and time. Also, their value does not change automatically when you modify other columns; to update them, you must change ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Reference Manual

MySQL Reference Manual

Michael Widenius, David Axmark, Kaj Arno
High Performance MySQL

High Performance MySQL

Jeremy D. Zawodny, Derek J. Balling
MySQL Stored Procedure Programming

MySQL Stored Procedure Programming

Guy Harrison, Steven Feuerstein

Publisher Resources

ISBN: 0596001452Catalog PageErrata