"JJ" <jj@nospam.com> wrote in message
news:Nj******************@newsfe1-win.ntli.net...
I'm using 5.0.22.
I understood that simply creating two columns with a TIMESTAMP type, will
result in the first column showing the update time and the second column
show the created time.
I can't get it to work at all. All I get is the updated time - the created
timestamp just ends up as 0000:00 etc.
I haven't altered the MaxDB setting from the default either?
??
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:
a.. For one TIMESTAMP column in a table, you can assign the current
timestamp as the default value and the auto-update value. It is possible to
have the current timestamp be the default value for initializing the column,
for the auto-update value, or both. It is not possible to have the current
timestamp be the default value for one column and the auto-update value for
another column.
b.. You can specify which TIMESTAMP column to automatically initialize or
update to the current date and time. This need not 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 used as the one that is
initialized to the current timestamp or updated automatically.
In a CREATE TABLE statement, the first TIMESTAMP column can be declared in
any of the following ways:
a.. 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.
b.. With neither DEFAULT nor ON UPDATE clauses, it is the same as DEFAULT
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.
c.. 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.
d.. With no DEFAULT clause and with an ON UPDATE CURRENT_TIMESTAMP clause,
the column has a default of 0 and is automatically updated.
e.. With a constant DEFAULT value, the column has the given default. If
the column has an ON UPDATE CURRENT_TIMESTAMP clause, it is automatically
updated, otherwise not.
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.)
CURRENT_TIMESTAMP or any of its synonyms (CURRENT_TIMESTAMP(), NOW(),
LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, or LOCALTIMESTAMP()) can be used in
the DEFAULT and ON UPDATE clauses. They all mean "the current timestamp."
(UTC_TIMESTAMP is not allowed. Its range of values does not align with those
of the TIMESTAMP column anyway unless the current time zone is UTC.)
The order of the DEFAULT and ON UPDATE attributes does not matter. If both
DEFAULT and ON UPDATE are specified for a TIMESTAMP column, either can
precede the other. For 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 if you omit both of the DEFAULT
and ON UPDATE clauses, 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 Section 5.10.8, "MySQL Server 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 constant,
you get back the same value you store. If you store a TIMESTAMP value, and
then change the time zone and retrieve the value, the retrieved value is
different than the value you stored. This occurs because the same time zone
was 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.
Note that a TIMESTAMP column which allows NULL values not take on the
current timestamp except under one of the following conditions:
a.. Its default value is defined as CURRENT_TIMESTAMP
b.. NOW() or CURRENT_TIMESTAMP is inserted into the column
In other words, a TIMESTAMP column defined as NULL will auto-initialize only
if it is created using a definition such as the following:
CREATE TABLE t (ts TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);
Otherwise - that is, if the TIMESTAMP column is defined to allow NULL values
but not using DEFAULT TIMESTAMP, as shown here.
CREATE TABLE t1 (ts TIMESTAMP NULL DEFAULT NULL);
CREATE TABLE t2 (ts TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00');
..then you must explicitly insert a value corresponding to the current date
and time, for example:
INSERT INTO t1 VALUES (NOW());
INSERT INTO t2 VALUES (CURRENT_TIMESTAMP);
