Another TIMESTAMP question

How do I set one field to have the updated timestamp, and another to have
the created timestamp?

I want to do this directly from code generated from DB Designer if
possible?!


JJ

Re: Another TIMESTAMP question

You must use ver 4.1.2 or higher. See:
http://dev.mysql.com/doc/refman/4.1/en/timestamp-4-1.html
//Ike

Re: Another TIMESTAMP question

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?
??


"Ike" wrote in message
news:ufAhg.10108$921.1928@newsread4.news.pas.earthlink.net.. .
> You must use ver 4.1.2 or higher. See:
> http://dev.mysql.com/doc/refman/4.1/en/timestamp-4-1.html
> //Ike
>
>

Re: Another TIMESTAMP question

"JJ" wrote in message
news:NjThg.17105$x53.6093@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);

Re: Another TIMESTAMP question

Thanks Ike,

But I've read all this and it still doesn't work. Have given up on try to
get the values to set automatically, will have to set them explicitly
evertime I update or add a record.

Thanks anyway,

JJ
"Ike" wrote in message
news:KJhig.3765$lf4.1883@newsread1.news.pas.earthlink.net...
>
> "JJ" wrote in message
> news:NjThg.17105$x53.6093@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);
>
>
>
>

Re: Another TIMESTAMP question

JJ wrote:
> 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.

The documentation is confusing, but the answer is simple. MySQL 5 only
allows for one column to use the TimeStamp for auto-updating. You can
have two, but one will always default to 0's, and hacking at the column
definition just brings an error.

A common solution is to manually set the dummy column with a NOW() in
the INSERT.

A more elegant solution with MySQL 5 is to use a trigger with regulat
DateTime columns for full control of whatever behavior you want. Here's
a nice article on that:
http://www.futhark.ch/mysql/108.html

Re: Another TIMESTAMP question

Thanks thats very helpful.

"Skarjune" wrote in message
news:1150919257.559292.230610@p79g2000cwp.googlegroups.com.. .
> JJ wrote:
>> 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.
>
> The documentation is confusing, but the answer is simple. MySQL 5 only
> allows for one column to use the TimeStamp for auto-updating. You can
> have two, but one will always default to 0's, and hacking at the column
> definition just brings an error.
>
> A common solution is to manually set the dummy column with a NOW() in
> the INSERT.
>
> A more elegant solution with MySQL 5 is to use a trigger with regulat
> DateTime columns for full control of whatever behavior you want. Here's
> a nice article on that:
> http://www.futhark.ch/mysql/108.html
>