By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,682 Members | 1,999 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,682 IT Pros & Developers. It's quick & easy.

Another TIMESTAMP question

P: n/a
JJ
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
Jun 6 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Ike
You must use ver 4.1.2 or higher. See:
http://dev.mysql.com/doc/refman/4.1/...stamp-4-1.html
//Ike
Jun 7 '06 #2

P: n/a
JJ
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" <rx*@hotmail.com> wrote in message
news:uf******************@newsread4.news.pas.earth link.net...
You must use ver 4.1.2 or higher. See:
http://dev.mysql.com/doc/refman/4.1/...stamp-4-1.html
//Ike

Jun 8 '06 #3

P: n/a
Ike

"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);


Jun 9 '06 #4

P: 1
I hated having to deal with 2 timestamp fields in mysql 4, since your using mysql5, take advantage of triggers. You can use 2 triggers to control exactly how you want the timestamp fields handled on record inserts and updates.

Full example:
http://www.codedumpster.com/sql/mysql5_timestamps
Jun 16 '06 #5

P: n/a
JJ
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" <rx*@hotmail.com> wrote in message
news:KJ*****************@newsread1.news.pas.earthl ink.net...

"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);

Jun 18 '06 #6

P: n/a
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

Jun 21 '06 #7

P: n/a
JJ
Thanks thats very helpful.

"Skarjune" <dh*@wordimage.com> wrote in message
news:11**********************@p79g2000cwp.googlegr oups.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

Jun 23 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.