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

Automatic Timestamp

P: n/a
Hello,

I am working with DB2 v9, and I am looking to create a TIMESTAMP
column in one of my tables that will automatically insert the current
date/time when I add a new row. Is there a way to do this by
specifying a generation formula, or must I create a trigger? Thank
you.

Jun 7 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Jeff Shantz wrote:
I am working with DB2 v9, and I am looking to create a TIMESTAMP
column in one of my tables that will automatically insert the current
date/time when I add a new row. Is there a way to do this by
specifying a generation formula, or must I create a trigger? Thank
you.
The easiest would be to use a default:

CREATE TABLE T(c1 TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP)

Or create a before trigger:
CREATE TRIGGER trg BEFORE INSERT ON T REFERENCING NEW AS n FOR EACH ROW
SET c1 = CURRENT TIMESTAMP

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jun 7 '07 #2

P: n/a
Serge Rielau wrote:
>I am working with DB2 v9, and I am looking to create a TIMESTAMP column ...
The easiest would be to use a default:

CREATE TABLE T(c1 TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP)
By the way, what about removing "CURRENT TIMESTAMP" from the above ?

| CREATE TABLE T(c1 TIMESTAMP WITH DEFAULT)

The documentation ( DB2 V9 IC/SQL/ALTER TABLE ) states default value : "For existing rows, a date corresponding to January 1, 0001, and a time corresponding to 0 hours, 0 minutes, 0 seconds and 0 microseconds." What this crypto means ? My experimentation did not clear it..
--
Konstantin Andreev.
Jun 7 '07 #3

P: n/a
Konstantin Andreev wrote:
Serge Rielau wrote:
The documentation ( DB2 V9 IC/SQL/ALTER TABLE ) states default value :
"For existing rows, a date corresponding to January 1, 0001, and a time
corresponding to 0 hours, 0 minutes, 0 seconds and 0 microseconds." What
this crypto means ? My experimentation did not clear it..
Documentation is correct. Here is an example:

---------------------------- Example start -----------------------------
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\>db2 connect to sample

Database Connection Information

Database server = DB2/NT x.x.x
SQL authorization ID = xxxxxxxx
Local database alias = SAMPLE
C:\>db2 create table t (c1 int with default)
DB20000I The SQL command completed successfully.
C:\>db2 insert into t values default
DB20000I The SQL command completed successfully.

C:\>db2 select * from t

C1
-----------
0

1 record(s) selected.
C:\>db2 alter table t add column c2 timestamp with default
DB20000I The SQL command completed successfully.
C:\>db2 insert into t values (1,default)
DB20000I The SQL command completed successfully.

C:\>db2 select * from t

C1 C2
----------- --------------------------
0 0001-01-01-00.00.00.000000
1 2007-06-07-18.37.02.993000

2 record(s) selected.

---------------------------- Example end -----------------------------

Perhaps I should visit you and we can exchange experiences?
Jan M. Nelken
Jun 7 '07 #4

P: n/a
Jan M. Nelken wrote:
Konstantin Andreev wrote:
>The documentation ( DB2 V9 IC/SQL/ALTER TABLE ) states default value :
"For existing rows, a date corresponding to January 1, 0001, and a time corresponding to 0 hours, 0 minutes, 0 seconds and 0 microseconds." What this crypto means ? My experimentation did not clear it..

Documentation is correct.
Correctness does not mean neither lucidity nor completeness :(
C:\>db2 create table t (c1 int with default)
C:\>db2 insert into t values default
C:\>db2 alter table t add column c2 timestamp with default
C:\>db2 insert into t values (1,default)
C:\>db2 select * from t

C1 C2
----------- --------------------------
0 0001-01-01-00.00.00.000000
1 2007-06-07-18.37.02.993000

Gotcha! The statement in question is about *added* columns, not about the columns the table was created with...
Thank you again, Jan.
--
Konstantin Andreev.
Jun 7 '07 #5

P: n/a
On 8 Juni, 00:06, Konstantin Andreev <feobalcaip...@datatech.ru>
wrote:
Serge Rielau wrote:
I am working with DB2 v9, and I am looking to create a TIMESTAMP column ...
The easiest would be to use a default:
CREATE TABLE T(c1 TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP)

By the way, what about removing "CURRENT TIMESTAMP" from the above ?

| CREATE TABLE T(c1 TIMESTAMP WITH DEFAULT)
[...]

Minor nitpick on the syntax used.

CREATE TABLE T(c1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP)

is standard compliant

/Lennart

[...]
Jun 8 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.