DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(mysql.info.gz) TIMESTAMP 4

Info Catalog (mysql.info.gz) TIMESTAMP pre-4 (mysql.info.gz) DATETIME
 
 11.3.1.2 `TIMESTAMP' Properties as of MySQL 4.1
 ...............................................
 
 In MySQL 4.1 and up, the properties of the `TIMESTAMP' column type
 change in the ways described in this section.
 
 From MySQL 4.1.0 on, `TIMESTAMP' display format differs from that of
 earlier MySQL releases:
 
    * `TIMESTAMP' columns are displayed in the same format as `DATETIME'
      columns.
 
    * Display widths (used as described in the preceding section) are no
      longer supported. In other words, for declarations such as
      `TIMESTAMP(2)', `TIMESTAMP(4)', and so on, the display width is
      ignored.
 
 
 Beginning with MySQL 4.1.1, the MySQL server can be run in `MAXDB'
 mode.  When the server runs in this mode, `TIMESTAMP' is identical with
 `DATETIME'. That is, if the server is running in `MAXDB' mode at the
 time that a table is created, `TIMESTAMP' columns are created as
 `DATETIME' columns.  As a result, such columns use `DATETIME' display
 format, have the same range of values, and there is no automatic
 initialization or updating to the current date and time.
 
 To enable `MAXDB' mode, set the server SQL mode to `MAXDB' at startup
 using the `--sql-mode=MAXDB' server option or by setting the global
 `sql_mode' variable at runtime:
 
      mysql> SET GLOBAL sql_mode=MAXDB;
 
 A client can cause the server to run in `MAXDB' mode for its own
 connection as follows:
 
      mysql> SET SESSION sql_mode=MAXDB;
 
 As of MySQL 5.0.2, MySQL will not accept timestamp values that includes
 a zero in the day or month column or values that are not a valid date.
 (The exception is the special value '0000-00-00 00:00:00'.)
 
 Beginning with MySQL 4.1.2, you have more flexible control over when
 automatic `TIMESTAMP' initialization and updating occur and which
 column should have those behaviors:
 
    * You can assign the current timestamp as the default value and the
      auto-update value, as before. But it is possible to have just one
      automatic behavior or the other, or neither of them.
 
    * You can specify which `TIMESTAMP' column to automatically
      initialize or update to the current date and time. This no longer
      need be the first `TIMESTAMP' column.
 
 
 The following discussion describes the revised syntax and behavior.
 Note that this information applies only to `TIMESTAMP' columns for
 tables not created with `MAXDB' mode enabled. As noted earlier in this
 section, `MAXDB' mode causes columns to be created as `DATETIME'
 columns.
 
 The following items summarize the pre-4.1.2 properties for `TIMESTAMP'
 initialization and updating:
 
 The first `TIMESTAMP' column in table row automatically is set to the
 current timestamp when the record is created if the column is set to
 `NULL' or is not specified at all.
 
 The first `TIMESTAMP' column in table row automatically is updated to
 the current timestamp when the value of any other column in the row is
 changed, unless the `TIMESTAMP' column explicitly is assigned a value
 other than `NULL'.
 
 If a `DEFAULT' value is specified for the first `TIMESTAMP' column when
 the table is created, it is silently ignored.
 
 Other `TIMESTAMP' columns in the table can be set to the current
 `TIMESTAMP' by assigning `NULL' to them, but they do not update
 automatically.
 
 As of 4.1.2, you have more flexibility in deciding which `TIMESTAMP'
 column automatically is initialized and updated to the current
 timestamp.  The rules are as follows:
 
 If a `DEFAULT' value is specified for the first `TIMESTAMP' column in a
 table, it is not ignored.  The default can be `CURRENT_TIMESTAMP' or a
 constant date and time value.
 
 `DEFAULT NULL' is the same as `DEFAULT CURRENT_TIMESTAMP' for the
 _first_ `TIMESTAMP' column.  For any other `TIMESTAMP' column, `DEFAULT
 NULL' is treated as `DEFAULT 0'.
 
 Any single `TIMESTAMP' column in a table can be set to be the one that
 is initialized to the current timestamp and/or updated automatically.
 
 In a `CREATE TABLE' statement, the first `TIMESTAMP' column can be
 declared in any of the following ways:
 
    * With both `DEFAULT CURRENT_TIMESTAMP' and `ON UPDATE
      CURRENT_TIMESTAMP' clauses, the column has the current timestamp
      for its default value, and is automatically updated.
 
    * With neither `DEFAULT' nor `ON UPDATE' clauses, it is the same as
      `DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'.
 
    * With a `DEFAULT CURRENT_TIMESTAMP' clause and no `ON UPDATE'
      clause, the column has the current timestamp for its default value
      but is not automatically updated.
 
    * With no `DEFAULT' clause and with an `ON UPDATE CURRENT_TIMESTAMP'
      clause, the column has a default of 0 and is automatically updated.
 
    * With a constant `DEFAULT' value and with `ON UPDATE
      CURRENT_TIMESTAMP' clause, the column has the given default and is
      automatically updated.
 
 
 In other words, you can use the current timestamp for both the initial
 value and the auto-update value, or either one, or neither.  (For
 example, you can specify `ON UPDATE' to get auto-update without also
 having the column auto-initialized.)
 
 Any of `CURRENT_TIMESTAMP', `CURRENT_TIMESTAMP()', or `NOW()' can be
 used in the `DEFAULT' and `ON UPDATE' clauses. They all have the same
 effect.
 
 The order of the two attributes does not matter.  If both `DEFAULT' and
 `ON UPDATE' are specified for a `TIMESTAMP' column, either can precede
 the other.
 
 Example.  These statements are equivalent:
 
      CREATE TABLE t (ts TIMESTAMP);
      CREATE TABLE t (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                                   ON UPDATE CURRENT_TIMESTAMP);
      CREATE TABLE t (ts TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                                   DEFAULT CURRENT_TIMESTAMP);
 
 To specify automatic default or updating for a `TIMESTAMP' column other
 than the first one, you must suppress the automatic initialization and
 update behaviors for the first `TIMESTAMP' column by explicitly
 assigning it a constant `DEFAULT' value (for example, `DEFAULT 0' or
 `DEFAULT '2003-01-01 00:00:00''). Then for the other `TIMESTAMP'
 column, the rules are the same as for the first `TIMESTAMP' column,
 except that you cannot omit both of the `DEFAULT' and `ON UPDATE'
 clauses.  If you do that, no automatic initialization or updating
 occurs.
 
 Example.  These statements are equivalent:
 
      CREATE TABLE t (
          ts1 TIMESTAMP DEFAULT 0,
          ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                        ON UPDATE CURRENT_TIMESTAMP);
      CREATE TABLE t (
          ts1 TIMESTAMP DEFAULT 0,
          ts2 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                        DEFAULT CURRENT_TIMESTAMP);
 
 Beginning with MySQL 4.1.3, you can set the current time zone on a
 per-connection basis, as described in `Time zone support'.  `TIMESTAMP'
 values still are stored in UTC, but are converted from the current time
 zone for storage, and converted back to the current time zone for
 retrieval.  As long as the time zone setting remains the same, you will
 get back the same value you store.  If you store a `TIMESTAMP' value,
 then change the time zone and retrieve the value, it will be different
 than the value you stored.  This occurs because the same time zone is
 not used for conversion in both directions.  The current time zone is
 available as the value of the `time_zone' system variable.
 
 Beginning with MySQL 4.1.6, you can include the `NULL' attribute in the
 definition of a `TIMESTAMP' column to allow the column to contain
 `NULL' values. For example:
 
      CREATE TABLE t
      (
      	ts1 TIMESTAMP NULL DEFAULT NULL,
      	ts2 TIMESTAMP NULL DEFAULT 0,
      	ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
      );
 
 Before MySQL 4.1.6 (and even as of 4.1.6 if the `NULL' attribute is not
 specified), setting the column to `NULL' sets it to the current
 timestamp.
 
Info Catalog (mysql.info.gz) TIMESTAMP pre-4 (mysql.info.gz) DATETIME
automatically generated byinfo2html