How Shall We Store it? – Datatypes - Database Design Manual: Using MySQL for Windows - page 28

row, or change the contents of a row, and do not explicitly change the contents of the first
TIMESTAMP column in that row, this column will be automatically updated with the date
and time of the change.
If, however, you explicitly set the value of the column when you are altering or creating
the row, the TIMESTAMP column will not auto-update because you have specified what you
want to go in it manually.
This can be a very useful column datatype as it will update itself without the need for
you to create code that will update it for you.
You define a TIMESTAMP column with a value, as follows:

columnname TIMESTAMP(length)

length refers to the number of characters that the column will output when retrieving its
value. For instance, of we were to store 28 February 2003, 01:50:59 in a TIMESTAMP(14)
column, we would obtain the following if we were to retrieve this value at a later time:

20030228015059

Table 3.5 shows the other values that would be returned if we were to store the same date
in timestamp columns declared with other lengths.

Table 3.5 TIMESTAMP datatype values.

Declaration DateTime returned

columnname TIMESTAMP(2) 02

(YY)

columnname TIMESTAMP(4) 0202

(YYMM)

columnname TIMESTAMP(6) 020228

(YYMMDD)

columnname TIMESTAMP(8) 20020228

(YYYYMMDD)

columnname TIMESTAMP(10) 0202280150

(YYMMDDHHMM)

columnname TIMESTAMP(12) 020228015059

(YYMMDDHHMMSS)

columnname TIMESTAMP(14) 20020228015059

(YYYYMMDDHHMMSS)

The length attribute in the TIMESTAMP declaration only alters the size of the string that
you get back when you retrieve the data. A TIMESTAMP row takes exactly the same storage
space irrespective of the length attribute specified when you declare it.

DATE
The DATE datatype is used when you are concerned about storing a date but do not need to
store the time as well. The DATE datatype is defined with no attributes as follows: