(mysql.info.gz) TIMESTAMP pre-4

 
 11.3.1.1 `TIMESTAMP' Properties Prior to MySQL 4.1
 ..................................................
 
 The `TIMESTAMP' column type provides a type that you can use to
 automatically mark `INSERT' or `UPDATE' operations with the current
 date and time.  If you have multiple `TIMESTAMP' columns in a table,
 only the first one is updated automatically. (From MySQL 4.1.2 on, you
 can specify which `TIMESTAMP' column updates;   
 TIMESTAMP 4.1.)
 
 Automatic updating of the first `TIMESTAMP' column in a table occurs
 under any of the following conditions:
 
    * You explicitly set the column to `NULL'.
 
    * The column is not specified explicitly in an `INSERT' or `LOAD
      DATA INFILE' statement.
 
    * The column is not specified explicitly in an `UPDATE' statement
      and some other column changes value.  An `UPDATE' that sets a
      column to the value it does not cause the `TIMESTAMP' column to be
      updated; if you set a column to its current value, MySQL ignores
      the update for efficiency.
 
 
 `TIMESTAMP' columns other than the first can also be set to the current
 date and time.  Just set the column to `NULL' or to any function that
 produces the current date and time (`NOW()', `CURRENT_TIMESTAMP').
 
 You can set any `TIMESTAMP' column to a value different from the current
 date and time by setting it explicitly to the desired value.  This is
 true even for the first `TIMESTAMP' column.  You can use this property
 if, for example, you want a `TIMESTAMP' to be set to the current date
 and time when you create a row, but not to be changed whenever the row
 is updated later:
 
    * Let MySQL set the column when the row is created.  This
      initializes it to the current date and time.
 
    * When you perform subsequent updates to other columns in the row,
      set the `TIMESTAMP' column explicitly to its current value:
 
           UPDATE TBL_NAME
               SET TIMESTAMP_COL = TIMESTAMP_COL,
                   OTHER_COL1 = NEW_VALUE1,
                   OTHER_COL2 = NEW_VALUE2, ...
 
 
 Another way to maintain a column that records row-creation time is to
 use a `DATETIME' column that you initialize to `NOW()' when the row is
 created and leave alone for subsequent updates.
 
 `TIMESTAMP' values may range from the beginning of 1970 to partway
 through the year 2037, with a resolution of one second.  Values are
 displayed as numbers.  When you store a value in a `TIMESTAMP' column,
 it is assumed to be represented in the current time zone, and is
 converted to UTC for storage. When you retrieve the value, it is
 converted from UTC back to the local time zone for display.  Before
 MySQL 4.1.3, the server has a single time zone. As of 4.1.3, clients
 can set their time zone on a per-connection basis, as described in
 `Time zone support'.
 
 The format in which MySQL retrieves and displays `TIMESTAMP' values
 depends on the display size, as illustrated by the following table.  The
 "full" `TIMESTAMP' format is 14 digits, but `TIMESTAMP' columns may be
 created with shorter display sizes:
 
 *Column Type*          *Display Format*
 `TIMESTAMP(14)'        `YYYYMMDDHHMMSS'
 `TIMESTAMP(12)'        `YYMMDDHHMMSS'
 `TIMESTAMP(10)'        `YYMMDDHHMM'
 `TIMESTAMP(8)'         `YYYYMMDD'
 `TIMESTAMP(6)'         `YYMMDD'
 `TIMESTAMP(4)'         `YYMM'
 `TIMESTAMP(2)'         `YY'
 
 All `TIMESTAMP' columns have the same storage size, regardless of
 display size.  The most common display sizes are 6, 8, 12, and 14.  You
 can specify an arbitrary display size at table creation time, but
 values of 0 or greater than 14 are coerced to 14.  Odd-valued sizes in
 the range from 1 to 13 are coerced to the next higher even number.
 
 `TIMESTAMP' columns store legal values using the full precision with
 which the value was specified, regardless of the display size.  This has
 several implications:
 
    * Always specify year, month, and day, even if your column types are
      `TIMESTAMP(4)' or `TIMESTAMP(2)'.  Otherwise, the value is not a
      legal date and `0' will be stored.
 
    * If you use `ALTER TABLE' to widen a narrow `TIMESTAMP' column,
      information will be displayed that previously was "hidden."
 
    * Similarly, narrowing a `TIMESTAMP' column does not cause
      information to be lost, except in the sense that less information
      is shown when the values are displayed.
 
    * Although `TIMESTAMP' values are stored to full precision, the only
      function that operates directly on the underlying stored value is
      `UNIX_TIMESTAMP()'.  Other functions operate on the formatted
      retrieved value.  This means you cannot use a function such as
      `HOUR()' or `SECOND()' unless the relevant part of the `TIMESTAMP'
      value is included in the formatted value.  For example, the `HH'
      part of a `TIMESTAMP' column is not displayed unless the display
      size is at least 10, so trying to use `HOUR()' on shorter
      `TIMESTAMP' values produces a meaningless result.
Info Catalog
(mysql.info.gz) DATETIME
(mysql.info.gz) TIMESTAMP 4
automatically generated byinfo2html